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