May 21, 2003 at 1:19 pm
Can I do this in SQL Server 2000 -
I want to set the value of a column in a trigger before it is inserted. I have a table that stores the next value for a column (basically a counter). I also have a stored procedure that will get the value and update the table. I want to insert a row into the table and have the trigger get the next value by calling the stored procedure. The column is new and has a NOT NULL constraint. I have existing code that inserts into the table and I don’t want to require code changes to specify this column. I was hoping to do it “behind the scenes”.
May 21, 2003 at 2:51 pm
Have you looked at instead of triggers?
Also, if you put a default on the column, you could use a regular trigger and update the value to the correct value
May 21, 2003 at 6:33 pm
quote:
Have you looked at instead of triggers?
It's possible that tkbr0wn meant to say, 'Have you looked at an Identity column instead of using triggers?' If this is so, you can use this column type to automagically get the next available integer value.
If that won't work for you, you could use a SELECT Max(KeyColumn) and add 1 to it to get the next available value. This, of course, requires a lock on the table to consistently return a unique value... if the column value must be unique.
SJTerrill
May 21, 2003 at 9:21 pm
Nope... I meant to say instead of.
There are regular triggers and there are instead of triggers. Look them up in BOL.
May 22, 2003 at 8:42 am
I am actually trying to replace some Identity columns with my own sequence. I have the stored procedure so that the management of the sequence is isolated and callable from the client. Because the column was an Identity before I need the trigger to get the value automatically to prevent existing code from breaking.
I had done the Instead Of Insert trigger but I was still required to pass a value for the column since it is Not Null. I haven’t tried the default on the column yet but I think that should work.
Thanks for the help!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply