Determining whether inserted or updated
-
Hi I wish to have a trigger on a particular table A whereby if the record is a new record an entry is made into another table B recording changes with a flag of "I" in one of the fields and if it is updated then a new record is placed into that table with a "U".
My initial thoughts as to determining wheteher the record is an insert is to join the inserted table back into table A to determine. am I missing something or is there an easier way to achieve this
-
You could use two triggers, one insert and one update trigger.
Alternatively, check the deleted table if you want to see whether the record has been updated.
An update operation consists of a delete and an insert operation so checking the inserted table won't tell you whether it's an insert (it could also have been an update).
Hope that helps.
-
Conversely, you could have 2 different SP's 1 for INSERT, 1 for UPDATE and inside of them write the appropriate data to the audit table. This would eliminate the trigger and avoid the possibility of TABLE LOCK while the TRIGGER is working....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Thanks AJ and Gram, I was trying to be a bit adventurous achieving it in the one trigger wasn't I
. I think I'll go the individual trigger route as you suggest -
>> I was trying to be a bit adventurous achieving it in the one trigger wasn't I <<
Not really. You can determine the operetaion mode in the trigger with:
declare @cntI int, @cntD int
select @cntI = count(*) from inserted
select @cntD = count(*) from deleted
if @cntI >0 and @cntD > 0
begin
-- Update Code
end
else if @cntI > 0 and @cntD = 0
begin
-- Insert Code
end
else if @cntI = 0 and @cntD > 0
begin
-- Delete Code
end
hth
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply