SQL Server Performance Forum – Threads Archive
trigger on updatehow can you make your trigger retrive a valuw from the record that fired the triger I am trying to create a triger ( insert, update ) that will excute some stored procedures but the stored procedures need the record ID to function correctly
( I managed to to get the ID (generated outomaticaly when insert) by
SELECT MAX(recordID) From tableName
the problem is that when I update any record it always return the same ID regardless of what record is actually been updated and always return the ID of the last record inserted in the table instead waleed
Can’t you reference the logical ‘deleted’ or ‘inserted’ table to get the value of the ID? You should be able to reference these tables in your trigger to interrogate the ID value.
Shaun World Domination Through Superior Software
OK I think I found a solution to my problem ( I am not 100% sure that this is the best way yet) DECLARE @RecordID tinyint
select @old_RecordID = RecordID from deleted
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by wels</i><br />DECLARE @RecordID tinyint<br />select @old_RecordID = RecordID from deleted<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />This will work if you had only one row deleted (updated/inserted). If more<br />than one row were deleted, @old_RecordID will hold the ID of the last row <br />in this result set. <br /><br />You have to make sure your solution covers both cases. You can tell how many <br />rows were affeted using @@rowcount inside the trigger.<br /><br />Don’t rush into openning a cursor, most of the time there’s a better solution <br />than that. <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />Bambola.<br /><br />Bambola.