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, Apr 14 '15 at 08:01
http://www.sql-server-helper.com/error-messages/msg-334.aspx This might help.. — Ambareesh Surendran, Apr 14 '15 at 08:14
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, Apr 14 '15 at 08:16
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, Apr 14 '15 at 08:17

score 0 · Answer 1 · answered Oct 19 '20 at 23:41

0

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.

answered Oct 19 '20 at 23:41

Ian Boyd

246,734
253
869
1,219

Output with trigger

1 Answers1