Finding the Event Which Fired the Trigger
-
Hi All,
Is there any way to find the event (Insert/Update/Delete) whcih fired the trigger on a table. ???? apart from writing separate trigger for each event ?
thanks
Jeswanth
--------------------------------
-
if you use the same trigger to handle the three possible events you can do this to determine what happened an act accordingly
declare @ins_cnt int, @del_cnt int
select @ins_cnt = count(*) from inserted
select @del_cnt = count(*) from deleted
if @ins_cnt > 0 and @del_cnt > 0
begin
-- Update Code Here
end
if @ins_cnt > 0 @del_cnt = 0
begin
-- Insert Code Here
end
if @ins_cnt = 0 @del_cnt > 0
begin
-- Delete Code Here
end
else
begin
-- nothing happened
return
end
hth
* Noel -
If you only need to differentiate between actions when there are rows affected by the triggering action, you can use something like this:
CREATER TRIGGER ...
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @InsertedCount int
, @DeletedCount int
SELECT @InsertedCount = count(*) FROM inserted
SELECT @DeletedCount = count(*) FROM deleted
IF @InsertedCount > 0 and @DeletedCount = 0
BEGIN
<code for inserts here>
END
ELSE IF @InsertedCount > 0 and @DeletedCount > 0
BEGIN
<code for updates here>
END
ELSE IF @InsertedCount = 0 and @DeletedCount > 0
BEGIN
<code for deletes here>
END
ELSE
BEGIN
<no rows affected code here if you need it>
END
END
This will probably not perform as well as independent queries, but that may not be an issue for your situation.
Scott Thornburg
-
Hi,
Thanks for the replies. but whats the ieda about the deleted count > 0 and inserted count > 0 . Is it happeing because when we update any row it will delete the row first and inserts again internally ?
Thanks again
Jeswanth
--------------------------------
-
Jeswanth,
The inserted and deleted virtual tables are nothing but special tables generated from the transaction log. Deep down an update is nothing but a delete followed by an insert. Therefore when you update a row you will get a representation of the "old" values in the deleted table and the "new" values in the inserted table
hth
* Noel -
Hi Noel.
Thanks a lot. Infact i wrote three Triggers! now i will change back to this method.
Thanks again
Jeswanth
--------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply