StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I assume you are on 2005 or better? If so, look into the OUTPUT clause, you shouldn't need row-level triggers. For example:</p> <pre><code>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; </code></pre> <p>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.</p> <pre><code>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; </code></pre> <p>(BTW, if you are going for identity values, use SCOPE_IDENTITY(), not @@IDENTITY.)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload