SQLTeam.com | Weblogs | Forums

AFTER INSERT trigger error: Subquery returned more than 1 value

I am creating an AFTER INSERT trigger to update a field called NLCODE in my table. If I insert a single row, it fires OK. But if I do a bulk insert, I get the error above. I have read up around this error and tried to use the RECID field in my subquery to join to the Inserted table but I am obviously doing it incorrectly (at the end).

I would appreciate any help in correcting my trigger code:

CREATE TRIGGER trg_UPDATESLNOMINAL ON INVITEMS
AFTER INSERT
AS
BEGIN
	SET NOCOUNT ON

	UPDATE inv
	SET inv.NLCODE = (
			SELECT CASE 
					--Multi Currency Within Depot Postcode Area. All Charges To Hiring Depot
					WHEN ih.currid <> 'GBP'
						AND ih.HIREDEPOT = da.depot
						AND ih.invtype < 5
						AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
							'5105'
							,'5101'
							,'5110'
							)
						THEN (left(ii.NLCODE, 3) + da.nlcc + substring(ii.NLCODE, 6, 10) + ih.CURRID)
							--Multi Currency Outside Depot Postcode Area - Transport Charges Remain With Hiring Depot
					WHEN ih.currid <> 'GBP'
						AND ih.HIREDEPOT <> da.depot
						AND ii.type IN (
							9
							,10
							)
						AND ih.invtype < 5
						AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
							'5105'
							,'5101'
							,'5110'
							)
						THEN (left(ii.NLCODE, 3) + d.nlcc + substring(ii.NLCODE, 6, 10) + ih.CURRID)
							--Multi Currency Outside Depot Postcode Area - All Charges Excluding Transport Are At Destination Depot
					WHEN ih.currid <> 'GBP'
						AND ih.HIREDEPOT <> da.depot
						AND ii.type NOT IN (
							9
							,10
							)
						AND ih.invtype < 5
						AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
							'5105'
							,'5101'
							,'5110'
							)
						THEN (left(ii.NLCODE, 3) + da.nlcc + substring(ii.NLCODE, 6, 10) + ih.CURRID)
							--Multi Currency and No Post Code Match
					WHEN ih.currid <> 'GBP'
						AND da.depot IS NULL
						AND ih.invtype < 5
						AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
							'5105'
							,'5101'
							,'5110'
							)
						THEN (left(ii.NLCODE, 3) + ds.nlcc + substring(ii.NLCODE, 6, 10) + ih.CURRID)
							--Multi Currency and is Workshop Job Charges Ardent Fitter
					WHEN ih.currid <> 'GBP'
						AND ih.invtype > 4
						AND eg.GROUPCODE <> 'TPE'
						AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
							'5105'
							,'5101'
							,'5110'
							)
						THEN (left(ii.NLCODE, 3) + dt.nlcc + substring(ii.NLCODE, 6, 10) + ih.CURRID)
							--Multi Currency and is Workshop Job Charges Third Party
					WHEN ih.currid <> 'GBP'
						AND ih.invtype > 4
						AND da.Depot IS NOT NULL
						AND ep.GROUPCODE = 'TPE'
						AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
							'5105'
							,'5101'
							,'5110'
							)
						THEN (left(ii.NLCODE, 3) + da.nlcc + substring(ii.NLCODE, 6, 10) + ih.CURRID)
							--Multi Currency and is Workshop Job Charges Third Party No Match
					WHEN ih.currid <> 'GBP'
						AND ih.invtype > 4
						AND da.Depot IS NULL
						AND ep.GROUPCODE = 'TPE'
						AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
							'5144'
							,'5105'
							,'6300'
							,'6035'
							)
						THEN (left(ii.NLCODE, 3) + ds.nlcc + substring(ii.NLCODE, 6, 10) + ih.CURRID)
							--Within Depot Postcode Area. All Charges To Hiring Depot -HIRE
					WHEN ih.currid = 'GBP'
						AND ih.HIREDEPOT = da.depot
						AND ih.invtype < 5
						AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
							'5105'
							,'5101'
							,'5110'
							)
						THEN (left(ii.NLCODE, 3) + da.nlcc + substring(ii.NLCODE, 6, 10))
							--Outside Depot Postcode Area - Transport Charges Remain With Hiring Depot
					WHEN ih.currid = 'GBP'
						AND ih.HIREDEPOT <> da.depot
						AND ii.[TYPE] IN (
							9
							,10
							)
						AND ih.INVTYPE < 5
						AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
							'5105'
							,'5101'
							,'5110'
							)
						THEN (left(ii.NLCODE, 3) + d.nlcc + substring(ii.NLCODE, 6, 10))
							--Outside Depot Postcode Area - All Charges Excluding Transport Are At Destination Depot
					WHEN ih.currid = 'GBP'
						AND ih.HIREDEPOT <> da.depot
						AND ii.[TYPE] NOT IN (
							9
							,10
							)
						AND ih.INVTYPE < 5
						AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
							'5105'
							,'5101'
							,'5110'
							)
						THEN (left(ii.NLCODE, 3) + da.nlcc + substring(ii.NLCODE, 6, 10))
							--GBP and No Post Code Match
					WHEN ih.currid = 'GBP'
						AND da.depot IS NULL
						AND ih.invtype < 5
						AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
							'5105'
							,'5101'
							,'5110'
							)
						THEN (left(ii.NLCODE, 3) + d.nlcc + substring(ii.NLCODE, 6, 10))
							--GBP and is Workshop Job Charges Ardent Fitter
					WHEN ih.currid = 'GBP'
						AND ih.invtype > 4
						AND eg.GROUPCODE IS NOT NULL
						AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
							'5105'
							,'5101'
							,'5110'
							)
						THEN (left(ii.NLCODE, 3) + dt.nlcc + substring(ii.NLCODE, 6, 10))
							--GBP and is Workshop Job Charges Third Party
					WHEN --ih.currid = 'GBP'
						ih.invtype > 4
						AND da.Depot IS NOT NULL
						AND ep.GROUPCODE = 'TPE'
						AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
							'5144'
							,'5105'
							,'6300'
							,'6035'
							)
						THEN (left(ii.NLCODE, 3) + da.nlcc + substring(ii.NLCODE, 6, 10))
							--GBP and is Workshop Job Charges Third Party No Match
					WHEN ih.currid = 'GBP'
						AND ih.invtype > 4
						AND da.Depot IS NULL
						AND ep.GROUPCODE = 'TPE'
						AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
							'5105'
							,'5101'
							,'5110'
							)
						THEN (left(ii.NLCODE, 3) + d.nlcc + substring(ii.NLCODE, 6, 10))
					WHEN SUBSTRING(ii.NLCODE, 7, 4) = '5105'
						THEN (left(ii.NLCODE, 3) + '99' + substring(ii.NLCODE, 6, 10))
					WHEN SUBSTRING(ii.NLCODE, 7, 4) IN (
							'5101'
							,'5110'
							)
						THEN (left(ii.NLCODE, 3) + '98' + substring(ii.NLCODE, 6, 10))
					ELSE ii.NLCODE
					END AS NLCODE
			FROM invitems ii
			JOIN invhdr ih ON ih.INVNO = ii.INVNO
			LEFT OUTER JOIN Depots d ON ih.hiredepot = d.code
			LEFT OUTER JOIN ContItems ci ON ii.CIRECID = ci.RECID
			LEFT OUTER JOIN popitm pi ON ci.PIRECID = pi.recid
			LEFT OUTER JOIN contracts c ON c.CONTNO = ci.CONTNO
			LEFT OUTER JOIN lookup l ON ih.ACCT = l.ACCT
			LEFT OUTER JOIN joballocations ja ON ja.recid = ii.CIRECID
			LEFT OUTER JOIN jobitems ji ON ji.recid = ja.parid
			LEFT OUTER JOIN jobhdr jh ON jh.recid = ji.parid
			LEFT OUTER JOIN (
				SELECT DISTINCT f.PARENTRECID
					,f.USERNAME
					,f.[TIMESTAMP]
				FROM (
					SELECT PARENTRECID
						,max([TIMESTAMP]) AS [DATE]
					FROM [MOBILEINFO].[DBO].[MOBILEACTION]
					GROUP BY PARENTRECID
					) AS x
				INNER JOIN [MOBILEINFO].[DBO].[MOBILEACTION] AS f ON f.PARENTRECID = x.PARENTRECID
					AND f.[TIMESTAMP] = x.[DATE]
				) ma ON ma.PARENTRECID = jh.RECID
			LEFT OUTER JOIN drivers dr ON dr.USERNAME = ma.USERNAME
			LEFT OUTER JOIN depots dt ON dt.CODE = dr.DEPOT
			LEFT OUTER JOIN stock sk ON sk.ITEMNO = ii.ITEMNO
			LEFT OUTER JOIN depots ds ON ds.CODE = sk.CURRDEPOT
			LEFT OUTER JOIN drivers dp ON dp.CODE = jh.JOBMGR
			LEFT OUTER JOIN EMPGROUPALOC eg ON eg.EMPCODE = dr.CODE
				AND eg.skill = 1
			LEFT OUTER JOIN EMPGROUPALOC ep ON ep.EMPCODE = dp.CODE
				AND ep.skill = 1
			LEFT JOIN [Reports].[tblDepotPCArea] da ON LEFT(ih.DELPCODE, CHARINDEX(' ', ih.DELPCODE) + 1) = da.pcdsector
			)
	FROM INVITEMS inv
	JOIN Inserted i ON i.RECID = inv.RECID
END

Many thanks
Martyn

That is a lot of logic to put into a trigger. That will wreak all sorts of havoc if the table is volatile. Is there any way you can move this into the insert itself? Otherwise, output the inserted records to a temp table and then perform an update afterwards

It can't be moved to the insert itself unfortunately. But output to temp table sounds like a good idea, can you suggest some example code for the output to temp table part of the trigger please? So I would then join to the temp table in my update to the live table, yes?

Many thanks

Here's a simplistic view of it

drop table a
    drop table b
    drop table #Output
    go

create table a(f1 int identity(1,1) primary key not null,
				f2 int not null,
				f3 nvarchar(20) null)

create table b (f2 int primary key not null,
				f3 nvarchar(20))

Create table #Output (f1 int, f2 int)
go

insert into b (f2, f3)
values (1, 'test1'),
		(2, 'test2'),
		(3, 'test3')
go

insert into a ( f2)
output inserted.f1, inserted.f2  into #Output (f1, f2)
values (1) ,
		(2)

select * from a
update a
   set f3 = b.f3	
   from a a
	join #Output o
		on a.f1 = o.f1
	join b b
		on o.f2 = b.f2

select * from a
1 Like

That's great, thank you. I think I see how to do it from that.

Kind regards
Martyn

Try this, using a standard JOIN:

CREATE TRIGGER trg_UPDATESLNOMINAL 
ON dbo.INVITEMS
AFTER INSERT
AS
SET NOCOUNT ON;

UPDATE inv
SET inv.NLCODE = determine_NLCODE.NLCODE
FROM inserted i
INNER JOIN INVITEMS inv ON i.RECID = inv.RECID
INNER JOIN (
		SELECT  ii.RECID,
                CASE 
				--Multi Currency Within Depot Postcode Area. All Charges To Hiring Depot
				WHEN ih.currid <> 'GBP'
					AND ih.HIREDEPOT = da.depot
					AND ih.invtype < 5
					AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
						'5105'
						,'5101'
						,'5110'
						)
					THEN (left(ii.NLCODE, 3) + da.nlcc + substring(ii.NLCODE, 6, 10) + ih.CURRID)
						--Multi Currency Outside Depot Postcode Area - Transport Charges Remain With Hiring Depot
				WHEN ih.currid <> 'GBP'
					AND ih.HIREDEPOT <> da.depot
					AND ii.type IN (
						9
						,10
						)
					AND ih.invtype < 5
					AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
						'5105'
						,'5101'
						,'5110'
						)
					THEN (left(ii.NLCODE, 3) + d.nlcc + substring(ii.NLCODE, 6, 10) + ih.CURRID)
						--Multi Currency Outside Depot Postcode Area - All Charges Excluding Transport Are At Destination Depot
				WHEN ih.currid <> 'GBP'
					AND ih.HIREDEPOT <> da.depot
					AND ii.type NOT IN (
						9
						,10
						)
					AND ih.invtype < 5
					AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
						'5105'
						,'5101'
						,'5110'
						)
					THEN (left(ii.NLCODE, 3) + da.nlcc + substring(ii.NLCODE, 6, 10) + ih.CURRID)
						--Multi Currency and No Post Code Match
				WHEN ih.currid <> 'GBP'
					AND da.depot IS NULL
					AND ih.invtype < 5
					AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
						'5105'
						,'5101'
						,'5110'
						)
					THEN (left(ii.NLCODE, 3) + ds.nlcc + substring(ii.NLCODE, 6, 10) + ih.CURRID)
						--Multi Currency and is Workshop Job Charges Ardent Fitter
				WHEN ih.currid <> 'GBP'
					AND ih.invtype > 4
					AND eg.GROUPCODE <> 'TPE'
					AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
						'5105'
						,'5101'
						,'5110'
						)
					THEN (left(ii.NLCODE, 3) + dt.nlcc + substring(ii.NLCODE, 6, 10) + ih.CURRID)
						--Multi Currency and is Workshop Job Charges Third Party
				WHEN ih.currid <> 'GBP'
					AND ih.invtype > 4
					AND da.Depot IS NOT NULL
					AND ep.GROUPCODE = 'TPE'
					AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
						'5105'
						,'5101'
						,'5110'
						)
					THEN (left(ii.NLCODE, 3) + da.nlcc + substring(ii.NLCODE, 6, 10) + ih.CURRID)
						--Multi Currency and is Workshop Job Charges Third Party No Match
				WHEN ih.currid <> 'GBP'
					AND ih.invtype > 4
					AND da.Depot IS NULL
					AND ep.GROUPCODE = 'TPE'
					AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
						'5144'
						,'5105'
						,'6300'
						,'6035'
						)
					THEN (left(ii.NLCODE, 3) + ds.nlcc + substring(ii.NLCODE, 6, 10) + ih.CURRID)
						--Within Depot Postcode Area. All Charges To Hiring Depot -HIRE
				WHEN ih.currid = 'GBP'
					AND ih.HIREDEPOT = da.depot
					AND ih.invtype < 5
					AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
						'5105'
						,'5101'
						,'5110'
						)
					THEN (left(ii.NLCODE, 3) + da.nlcc + substring(ii.NLCODE, 6, 10))
						--Outside Depot Postcode Area - Transport Charges Remain With Hiring Depot
				WHEN ih.currid = 'GBP'
					AND ih.HIREDEPOT <> da.depot
					AND ii.[TYPE] IN (
						9
						,10
						)
					AND ih.INVTYPE < 5
					AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
						'5105'
						,'5101'
						,'5110'
						)
					THEN (left(ii.NLCODE, 3) + d.nlcc + substring(ii.NLCODE, 6, 10))
						--Outside Depot Postcode Area - All Charges Excluding Transport Are At Destination Depot
				WHEN ih.currid = 'GBP'
					AND ih.HIREDEPOT <> da.depot
					AND ii.[TYPE] NOT IN (
						9
						,10
						)
					AND ih.INVTYPE < 5
					AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
						'5105'
						,'5101'
						,'5110'
						)
					THEN (left(ii.NLCODE, 3) + da.nlcc + substring(ii.NLCODE, 6, 10))
						--GBP and No Post Code Match
				WHEN ih.currid = 'GBP'
					AND da.depot IS NULL
					AND ih.invtype < 5
					AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
						'5105'
						,'5101'
						,'5110'
						)
					THEN (left(ii.NLCODE, 3) + d.nlcc + substring(ii.NLCODE, 6, 10))
						--GBP and is Workshop Job Charges Ardent Fitter
				WHEN ih.currid = 'GBP'
					AND ih.invtype > 4
					AND eg.GROUPCODE IS NOT NULL
					AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
						'5105'
						,'5101'
						,'5110'
						)
					THEN (left(ii.NLCODE, 3) + dt.nlcc + substring(ii.NLCODE, 6, 10))
						--GBP and is Workshop Job Charges Third Party
				WHEN --ih.currid = 'GBP'
					ih.invtype > 4
					AND da.Depot IS NOT NULL
					AND ep.GROUPCODE = 'TPE'
					AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
						'5144'
						,'5105'
						,'6300'
						,'6035'
						)
					THEN (left(ii.NLCODE, 3) + da.nlcc + substring(ii.NLCODE, 6, 10))
						--GBP and is Workshop Job Charges Third Party No Match
				WHEN ih.currid = 'GBP'
					AND ih.invtype > 4
					AND da.Depot IS NULL
					AND ep.GROUPCODE = 'TPE'
					AND SUBSTRING(ii.NLCODE, 7, 4) NOT IN (
						'5105'
						,'5101'
						,'5110'
						)
					THEN (left(ii.NLCODE, 3) + d.nlcc + substring(ii.NLCODE, 6, 10))
				WHEN SUBSTRING(ii.NLCODE, 7, 4) = '5105'
					THEN (left(ii.NLCODE, 3) + '99' + substring(ii.NLCODE, 6, 10))
				WHEN SUBSTRING(ii.NLCODE, 7, 4) IN (
						'5101'
						,'5110'
						)
					THEN (left(ii.NLCODE, 3) + '98' + substring(ii.NLCODE, 6, 10))
				ELSE ii.NLCODE
				END AS NLCODE
		FROM invitems ii
		JOIN invhdr ih ON ih.INVNO = ii.INVNO
		LEFT OUTER JOIN Depots d ON ih.hiredepot = d.code
		LEFT OUTER JOIN ContItems ci ON ii.CIRECID = ci.RECID
		LEFT OUTER JOIN popitm pi ON ci.PIRECID = pi.recid
		LEFT OUTER JOIN contracts c ON c.CONTNO = ci.CONTNO
		LEFT OUTER JOIN lookup l ON ih.ACCT = l.ACCT
		LEFT OUTER JOIN joballocations ja ON ja.recid = ii.CIRECID
		LEFT OUTER JOIN jobitems ji ON ji.recid = ja.parid
		LEFT OUTER JOIN jobhdr jh ON jh.recid = ji.parid
		LEFT OUTER JOIN (
			SELECT DISTINCT f.PARENTRECID
				,f.USERNAME
				,f.[TIMESTAMP]
			FROM (
				SELECT PARENTRECID
					,max([TIMESTAMP]) AS [DATE]
				FROM [MOBILEINFO].[DBO].[MOBILEACTION]
				GROUP BY PARENTRECID
				) AS x
			INNER JOIN [MOBILEINFO].[DBO].[MOBILEACTION] AS f ON f.PARENTRECID = x.PARENTRECID
				AND f.[TIMESTAMP] = x.[DATE]
			) ma ON ma.PARENTRECID = jh.RECID
		LEFT OUTER JOIN drivers dr ON dr.USERNAME = ma.USERNAME
		LEFT OUTER JOIN depots dt ON dt.CODE = dr.DEPOT
		LEFT OUTER JOIN stock sk ON sk.ITEMNO = ii.ITEMNO
		LEFT OUTER JOIN depots ds ON ds.CODE = sk.CURRDEPOT
		LEFT OUTER JOIN drivers dp ON dp.CODE = jh.JOBMGR
		LEFT OUTER JOIN EMPGROUPALOC eg ON eg.EMPCODE = dr.CODE
			AND eg.skill = 1
		LEFT OUTER JOIN EMPGROUPALOC ep ON ep.EMPCODE = dp.CODE
			AND ep.skill = 1
		LEFT JOIN [Reports].[tblDepotPCArea] da ON LEFT(ih.DELPCODE, CHARINDEX(' ', ih.DELPCODE) + 1) = da.pcdsector
) AS determine_NLCODE ON determine_NLCODE.RECID = i.RECID

/*end of trigger*/
GO
1 Like