How to create trigger for each row in SQL Server

Question

create table t1(col int);
create table t2(col int);


CREATE TRIGGER tr
ON t1
AFTER INSERT 
as
    begin
    INSERT INTO t2(col) values(1)
end;

-- tables and trigger created

INSERT INTO t1(col) values(1), (2)

When I run this insert query, in table t2 insert event happens just once.

Needed FOR EACH ROW statement in trigger, but how to make this in SQL Server? Not found in documentation, and after googling, not found some good solution also.

If you really need them you must work with cursors: stackoverflow.com/questions/5805413/… — user_0
– user_0, Commented Jun 22, 2015 at 10:39
@a_horse_with_no_name: Dear using INSERTED table you can work row by row. Or I don't understand the question? — Joe Taras
– Joe Taras, Commented Jun 22, 2015 at 10:41
I'm voting to close this question as off-topic because this is a feature that's just not present in the product - you cannot "magically" make it appear... — marc_s
– marc_s, Commented Jun 22, 2015 at 10:47
Sounds more like an xy problem. Why do you need a row-by-row trigger? Just about anything you could want to do can be done in a set-based way with the table trigger. What are you trying to achieve, @otariki? — Tab Alleman
– Tab Alleman, Commented Jun 22, 2015 at 14:07

Joe Taras · Accepted Answer · 2015-06-22 10:38:49Z

3

Replace your: INSERT INTO t2(col) values(1)

in:

INSERT INTO t2(col) SELECT col FROM inserted

Because in inserted table you'll find all rows inserted in table linked at your trigger (t1)

answered Jun 22, 2015 at 10:38

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create trigger for each row in SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related