Trigger problem.

[jjlbmcnews]

I get the following error when my trigger executes on an insert - SUBQUERY RETURNED MORE THAN 1 VALUE. THIS IS NOT PERMITTED WHEN THE SUBQUERY FOLLOWS =, !=, <, <= , >, >= OR WHEN THE SUBQUERY IS USED AS AN EXPRESSION

My application uploads data to the database and then the trigger fires of when the data is inserted, which all works fine, only problem is if I upload multiple rows under one transaction I get the above error, is there away to get around this?

 

[SQLSister]

You need to post your trigger code for us to help.

 

[jjlbmcnews]

Code as requested.


CREATE TRIGGER [DistInsert] ON [dbo].[Distribution]
FOR INSERT
AS

/* Set these options to ON to resolve OLE DB error. */

SET XACT_ABORT ON

SET NOCOUNT ON

/* The code below extracts and breaks up the required data from the comment field */

IF @@TRANCOUNT > 0

BEGIN TRANSACTION

DECLARE @Headline VARCHAR(255)
DECLARE @ArticleDate VARCHAR(255)
DECLARE @Publication VARCHAR(255)
DECLARE @Edition VARCHAR(255)
DECLARE @Circulation VARCHAR(255)
DECLARE @Page VARCHAR(255)
DECLARE @PubCode VARCHAR(255)
DECLARE @Author VARCHAR(255)
DECLARE @Country VARCHAR(255)
DECLARE @Custom4 VARCHAR(255)
DECLARE @Convert VARCHAR(255)

SET @Headline = (SELECT SUBSTRING(Comment,CHARINDEX('<Headline>', Comment)+10,CHARINDEX('</Headline>', Comment)
-CHARINDEX('<Headline>',Comment)-10)
FROM Blackbird_Remote.dbo.Distribution)

SET @ArticleDate = (SELECT CONVERT(DATETIME,SUBSTRING(Comment,CHARINDEX('<Date>', Comment)+6,
CHARINDEX('</Date>', Comment)-CHARINDEX('<Date>',Comment)-6),104)
FROM Blackbird_Remote.dbo.Distribution)

SET @Publication = (SELECT SUBSTRING(Comment,CHARINDEX('<Source>', Comment)+8,CHARINDEX('</Source>',
Comment)-CHARINDEX('<Source>',Comment)-8)
FROM Blackbird_Remote.dbo.Distribution)

SET @Edition = (SELECT SUBSTRING(Comment,CHARINDEX('<Edition>', Comment)+9,CHARINDEX('</Edition>',
Comment)-CHARINDEX('<Edition>',Comment)-9)
FROM Blackbird_Remote.dbo.Distribution)

SET @Circulation = (SELECT SUBSTRING(Comment,CHARINDEX('<Circulation>', Comment)+13,
CHARINDEX('</Circulation>', Comment)-CHARINDEX('<Circulation>',Comment)-13)
FROM Blackbird_Remote.dbo.Distribution)

SET @Convert = (SELECT CONVERT(INT,REPLACE(CONVERT(VARCHAR(255),@Circulation),',','')))

SET @Page = (SELECT SUBSTRING(Comment,CHARINDEX('<page>', Comment)+6,CHARINDEX('</page>', Comment)
-CHARINDEX('<page>',Comment)-6)
FROM Blackbird_Remote.dbo.Distribution)

SET @Pubcode = (SELECT SUBSTRING(Comment,CHARINDEX('<Sourcecode>', Comment)+12,CHARINDEX('</SourceCode>',
Comment)-CHARINDEX('<SourceCode>',Comment)-12)
FROM Blackbird_Remote.dbo.Distribution)

SET @Author = (SELECT SUBSTRING(Comment,CHARINDEX('<Author>', Comment)+8,CHARINDEX('</Author>',
Comment)-CHARINDEX('<Author>',Comment)-8)
FROM Blackbird_Remote.dbo.Distribution)

SET @Country = (SELECT SUBSTRING(Comment,CHARINDEX('<Country>', Comment)+9,CHARINDEX('</Country>', Comment)
-CHARINDEX('<Country>',Comment)-9)
FROM Blackbird_Remote.dbo.Distribution)

SET @Custom4 = ' '

/* Select and insert records from the Blackbird_Remote
distribution table into Blackbird distribution table */

INSERT INTO [SVR-SQL01].[Blackbird].[dbo].[Distribution]
([FName],
[Path],
[Date],
[Size],
[TextHits],
[Links],
[Comment],
[AlertID],
[HitCount],
[FileCount],
[IndexID],
[ic1],
[UserID],
[UserName],
[Account],
[Department],
[DistrDir],
[Custom1],
[Custom2],
[Custom3],
[Custom4],
[Custom5],
[Custom6],
[SummaryDoc],
[DBF_ID],
[Previous],
[QA_User],
[QA_Actioned],
[Headline],
[ArticleDate],
[Publication],
[Edition],
[Circulation],
[Page],
[PubCode],
[Author],
[Country],
[Query])
SELECT
.[FName],
.[Path],
.[Date],
.[Size],
.[TextHits],
.[Links],
.[Comment],
.[AlertID],
.[HitCount],
.[FileCount],
.[IndexID],
.[ic1],
.[UserID],
.[UserName],
.[Account],
.[Department],
.[DistrDir],
.[Custom1],
.[Custom2],
.[Custom3],
@Custom4,
.[Custom5],
.[Custom6],
.[SummaryDoc],
.[DBF_ID],
.[Previous],
.[QA_User],
.[QA_Actioned],
@Headline,
@ArticleDate,
@Publication,
@Edition,
@Convert,
@Page,
@PubCode,
@Author,
@Country,
.[Query]
FROM Blackbird_Remote.dbo.Distribution AS B

/* If an error occurs rollback the transaction, if no error then commit
the transaction*/

IF @@Error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION

 

[skicamel]

This is what I'd used a while back to fix a similar problem w/ a delete trigger. The Sister is right, though...Could use some code to look at, but see if this might help you out...

CREATE TRIGGER delcr ON whatevertable
FOR DELETE
AS
DECLARE @ROWCOUNT INT
SELECT * into #tempdel from deleted
select @rowcount = @@rowcount from #tempdel

while @rowcount > 0

begin

INSERT INTO deletedtable
SELECT whatevercolumns
FROM #tempdel
where ID = (select max(ID) from #tempdel)
delete from #tempdel where ID = (select max(ID) from #tempdel)
select @rowcount = @rowcount -1

end

 

[malexanian]

You must have multiple rows in Blackbird_Remote.dbo. Distribution. This causes an error because the subquery in your set @localvar statements must return one value and only one value in order to be assigned to @localvar.

First of all, do you expect inserts to happen only one record at a time? If so, this approach can still work. Is Blackbird_Remote.dbo.Distribution the table that you have the INSERT trigger on (your declaration is ambiguous here)? If so, try running the subquery off of the 'inserted' table instead. This is an in-memory table that stores a copy of every row being inserted. If you are restricting inserts to one record at a time, you will only get one row when you run your subquery.

If multiple row inserts can happen, you will have to find some way to do the multiple inserts one record at a time. The way that is least disruptive to your existing code (not necessarily the best) is to declare a cursor and step through the 'inserted' table one record at a time and do the insert into [SVR-SQL01].[Blackbird].[dbo].[Distribution] one record at a time.

Personally, disassembling XML belongs in the business layer and I wouldn't implement it in an SP. There are standard COM libraries that can do this with code that is much easier to maintain than the string functions you have above.

 

[jjlbmcnews]

Hi malexanian,

Thanks for your advice, problem is that the data I extract isn't an XML file or anything, one of the programs we run here uses those tags, why I don't know! but it would have been easier not to! and to top it all off it inserts it into one field when I need it in many. Anyway I do expect, on rare occasions, multiple row inserts and as for declaring a cursor I wouldn't know where to start!

 

[malexanian]

Is this trigger being fired on Blackbird_Remote? Please describe the business use case around the insert. We can then come up with the simplest, most elegant solution from there.