Output with trigger

Question

I have a table Test with columns

InveCode varchar(50)
itemid numeric(2)
rowid numeric identity,
purdate date

I have a trigger on this table.

Create trigger [dbo].[TrgInveCode] 
on [dbo].[test]
after insert
as
   declare @itemid numeric(10)
   declare @total numeric(10)
   declare @rowid numeric(18)
   declare @Date date

   select @itemid = i.itemid from inserted i
   select @rowid = i.rowid from inserted i
   select @Date = i.purdate from inserted i

   select @total = count(itemid) 
   from test 
   where itemid = @itemid and year(purdate) = year(@date)

   update test 
   set invecode= cast(@itemid as varchar(50)) + '-' + cast(year(@Date) as varchar(50)) + '-' + cast(@total as varchar(50)) 
   where rowid = @rowid

When I use this command

insert into test(itemid, purdate, buyingyear) 
output inserted.invecode 
values(@itemid, @purdate, @buyingyear)

in vb.net, I get this error:

The targer table 'test' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Which dbms? (Most dbms products' triggers are not ANSI SQL...) — jarlh
sql-server-helper.com/error-messages/msg-334.aspx This might help.. — Ambareesh Surendran
Your trigger has MAJOR flaw in that you seem to assume it'll be called once per row - that is not the case. The trigger will fire once per statement, so if your UPDATE statements affects 25 rows, you'll get the trigger fired once, but then Inserted and Deleted will each contain 25 rows. WHich of those 25 rows will your code select here?? SELECT i.RowId FROM Inserted i - it's non-deterministic, you'll get an arbitrary row, and the other 24 will be ignored. You need to rewrite your trigger to take this into account! — marc_s
The error is pretty clear: if you have a trigger on the table, you cannot use the OUTPUT clause in that way. Either use OUTPUT inserted.invecode INTO ......... or don't use OUTPUT at all — marc_s

Ian Boyd Ian Boyd · Accepted Answer · 2020-10-19T23:41:59

You cannot use OUTPUT clause on a table that has a trigger.

Cannot use UPDATE with OUTPUT clause when a trigger is on the table

You have no choice but to OUTPUT the results INTO a table variable.

Output with trigger

1 Answers