swtirs
asked on
How to identify what action caused the trigger to fire
I have a common trigger that is executed after insert, update and delete. I need to know what action caused the trigger to fire. for example the trigger fired becaused of a row deleted or bcos a row added etc.
I do not think there is an "updated" table in a trigger.
But if you have a primary key you might be able to use something like the idea. Choose a column, say pkcol, that is part of the PK, and say it's an int. (I have not tested this)
declare @pkinserted int
declare @pkdeleted int
set @pkinserted = pkcol from inserted
set @pkdeleted = pkcol from deleted
if @pkinserted is null and @pkdeleted is not null
begin
-- trigger is from delete
end
if @pkinserted is not null and @pkdeleted is not null
begin
-- trigger is from update
end
if @pkinserted is not null and @pkdeleted is null
begin
-- trigger is from insert
end
But if you have a primary key you might be able to use something like the idea. Choose a column, say pkcol, that is part of the PK, and say it's an int. (I have not tested this)
declare @pkinserted int
declare @pkdeleted int
set @pkinserted = pkcol from inserted
set @pkdeleted = pkcol from deleted
if @pkinserted is null and @pkdeleted is not null
begin
-- trigger is from delete
end
if @pkinserted is not null and @pkdeleted is not null
begin
-- trigger is from update
end
if @pkinserted is not null and @pkdeleted is null
begin
-- trigger is from insert
end
If it was an insert there will be rows in INSERTED and no rows in DELETED
If it was an update there will be rows in INSERTED and rows in DELETED
If it was a deletion there will be rows in DELETED and no rows in INSERTED.
These are all 'virtual tables' accessible from your trigger.
If it was an update there will be rows in INSERTED and rows in DELETED
If it was a deletion there will be rows in DELETED and no rows in INSERTED.
These are all 'virtual tables' accessible from your trigger.
If you full outer join the inserted and deleted tables on the primary key you can tell.
EG:
SELECT CASE WHEN d.ID IS NULL THEN 'inserted' WHEN i.ID IS NULL THEN 'deleted' ELSE 'updated' END TypeOfOperation
FROM INSERTED i
FULL OUTER JOIN DELETED d ON i.ID=d.ID
EG:
SELECT CASE WHEN d.ID IS NULL THEN 'inserted' WHEN i.ID IS NULL THEN 'deleted' ELSE 'updated' END TypeOfOperation
FROM INSERTED i
FULL OUTER JOIN DELETED d ON i.ID=d.ID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Perhaps, I am being stupid but. Having mulled over this question all afternoon, I am still biemused as to this question..........
Why do you need to know what operation caused the trigger to fire?
Why dont you simply have an INSERT trigger and UPDATE trigger and a DELETE trigger?
Why do you need to know what operation caused the trigger to fire?
Why dont you simply have an INSERT trigger and UPDATE trigger and a DELETE trigger?
INSERT INTO Table1(INSERT)
SELECT COUNT(*)
FROM Inserted
u can alse do this for "deleted" and "updated" trigger tables