Sql – Alternative to row level triggers

sqlsql-servertriggers

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

Best Solution

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.)

Related Question