[thelist] Oh m'gwad - its tip!

<TIP AUTHOR="Anthony Baratta" SUBJECT="@@Identity and Triggers">

 -=-=-=- MS SQL and ASP/VB Scripting -=-=-=-=-

With all the banter about @@Identity and how to use it to get your AutoSequence
NUmber from an Insert into a database - I have a gotcha you might be interested in. 

Don't put triggers on the table you are attempting to get the @@Identity from. The
trigger will fire off on the Insert and when you grab the @@Identity you will get
AutoSequence number from the 'last' table to have been touched by the trigger (if it
has one).

This burned me this weekend. I had a fairly large trigger, cascading data into a set
of tables based upon the date I inserted into the 'main' table. I then wanted the
@@Identity given to the Insert of the main table, yet got the @@Identity of the last
table touched by the Trigger and of course got the wrong number.

You are warned. ;-) 

</TIP>
-- 
Anthony Baratta
President
KeyBoard Jockeys
                    South Park Speaks Version 3 is here!!!
                       http://www.baratta.com/southpark
                              Powered by Tsunami