Alternative to row level triggers?

Question

MS SQL Server doesn't have row level triggers, correct? If I needed to insert a row from within a trigger and then insert another row, based on the result of the first insert, would a cursor be the best solution?

For example, is there a better way to do this:

CREATE TABLE t1 (foo int)
CREATE TABLE t2 (id int IDENTITY, foo int)
CREATE TABLE t3 (t2_id int)

GO

CREATE TRIGGER t1_insert_trg ON t1 FOR INSERT AS
    DECLARE c CURSOR FOR
     SELECT foo FROM inserted
    DECLARE @foo int
    OPEN c
    FETCH NEXT FROM c INTO @foo
    WHILE @@FETCH_STATUS = 0
    BEGIN
     INSERT INTO t2 (foo) VALUES (@foo)
     INSERT INTO t3 (t2_id) VALUES (@@IDENTITY)
     FETCH NEXT FROM c INTO @foo
    END
    CLOSE c
    DEALLOCATE c

Answer 1

Why not cascade the triggers - Use an INSERT trigger on T2 to perform the insert on T3. Then you can avoid the cursor within t1_insert_trg and just use inserted - as in:

CREATE TRIGGER t1_insert_trg ON t1 FOR INSERT AS
    INSERT INTO t2
    SELECT foo FROM inserted -- fires t2 INSERTED trigger 

CREATE TRIGGER t2_insert_trg ON t2 FOR INSERT AS
    INSERT INTO t3 
    SELECT id FROM inserted

Answer 2

I assume you are on 2005 or better? If so, look into the OUTPUT clause, you shouldn't need row-level triggers. For example:

USE tempdb;
GO

CREATE TABLE t1 (foo int);
CREATE TABLE t2 (id int IDENTITY, foo int);
CREATE TABLE t3 (t2_id int);
GO

CREATE TRIGGER t1_insert ON t1
FOR INSERT AS
BEGIN   
    DECLARE @new_rows TABLE(new_id INT, old_foo INT);

    INSERT t2(foo)
      OUTPUT inserted.id, inserted.foo 
      INTO @new_rows
    SELECT foo
    FROM inserted;

    INSERT t3 SELECT new_id FROM @new_rows;
END
GO

INSERT t1(foo) SELECT 1 UNION ALL SELECT 5;
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
GO

DROP TABLE t1,t2,t3;

You could also manage this by having a trigger on T1 that inserts into T2, then a trigger on T2 that inserts into T3. This isn't going to be as efficient IMHO, and is not easier to manage, but I will submit that it is easier to follow (and may be your only option if you are stuck on 2000). Both could be set-based and wouldn't need cursors or any other row-by-row processing method.

USE tempdb;
GO

CREATE TABLE t1 (foo int);
CREATE TABLE t2 (id int IDENTITY, foo int);
CREATE TABLE t3 (t2_id int);
GO

CREATE TRIGGER t1_insert ON t1
FOR INSERT AS
BEGIN    
    INSERT t2(foo)
    SELECT foo FROM inserted;
END
GO

CREATE TRIGGER t2_insert ON t2
FOR INSERT AS
BEGIN
    INSERT t3(t2_id)
    SELECT id FROM inserted;
END
GO

INSERT t1(foo) SELECT 1 UNION ALL SELECT 5;
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
GO

DROP TABLE t1,t2,t3;

(BTW, if you are going for identity values, use SCOPE_IDENTITY(), not @@IDENTITY.)

Answer 3

You might be able to avoid a cursor or the need to know what identity was inserted using the following inserts.

Insert INTO t2 (foo) Select foo from inserted
Insert into t3 (t2_id) Select t2.id from t2
inner join inserted  i on t2.foo = i.foo