Hello all.
I wish to use a single trigger for all Update/Insert/Delete events.
In order to do so I need a way of determining which event has caused the combined trigger to fire.
Based upon the event I can then determine which tables (Inserted or Deleted) I need to reference.
So I will have something like ;
CREATETRIGGER [Test_Trigger]
ON [dbo].[Table]
AFTER INSERT,UPDATE,DELETE
and will need to do something like ;
AS
BEGIN
IF <Insert>
Do something with INSERTED
ELSE
IF <Delete>
Do something with DELETED
I dont really want to create 3 triggers for each event
Many thanks
If INSERTED is populated and DELETED is not, then it's an insert
If INSERTED is not populated and DELETED is, then it's a delete
If both are populated, it's an UPDATE
|||
Normally, there are very distinct and different actions to be taken determined by the type of TRIGGER action. And normally, it is more efficient to have separate TRIGGERs. If you have additional code, even with good IF conditions and good flow control, you are having to execute unnecessary code EVERY time there is a table action.
Consider that you want a taxi to travel from point A to point D. Every time you travel there, the taxi goes by Point B and Point C (not a straight line) -it doesn't stop, but it goes by those places also. Was that efficient? Are you wasting time? Would you have prefered to travel straight from Point A to Point D?
The exception is for creating a history or 'audit' trail. In that case, it is usually enough to handle the 'deleted' table to store the previous data state (the current data state is in the table).
|||
You can use Inserted & Deleted logical (conceptual) tables to identify the current operation.
Yes, as Arine says you can have seperate triggers for each operation. It will reduce the additional overload of your query.
The sample as follow as,
Code Snippet
Create Trigger ..
..
..
Begin
Declare @.Flag as Int;
Select Top 1 @.Flag = 1 From Inserted;
Select Top 1 @.Flag = Isnull(@.Flag, 0) + 2 From Deleted;
If @.Flag = 1
Begin
--'Insert Operation Performed'
Return;
End
If @.Flag = 2
Begin
-- 'Delete Operation Performed'
Return;
End
If @.Flag = 3
Begin
-- 'Update Operation Performed'
Return;
End
End
|||
Thank you all for your comments.
I have done it, it is essentially what DaleJ also suggested.
This is what I did ;
DECLARE @.InsCount_T int
DECLARE @.DelCount_T int
SELECT @.InsCount_T =Count(*)FROM INSERTED
SELECT @.DelCount_T =Count(*)FROM DELETED
IF @.InsCount_T > @.DelCount_T --Insert
INSERTINTO dbo.Audit
SELECT*FROM INSERTED
ELSE
IF @.InsCount_T = @.DelCount_T --Update
INSERTINTO dbo.Audit
SELECT*FROM DELETED
ELSE
IF @.InsCount_T < @.DelCount_T --Delete
INSERTINTO dbo.Audit
SELECT*FROM DELETED
I understand what Arnie says about performance, however as with most things there is a trade-off between performance and maintainability. One trigger is easier to maintain and I dont imagine that the performance hit would be that great given the code above and the fact that a trigger will fire in any case. The only extra bit are the Counts on the tables. In my case I think a minor performance hit is acceptable given the number of users, potential additions/updates/deletes and the number of databases currently on server. But thanks for the word of warning, I'll certainly bear it in mind for more high usage db's. Manivannan's code is probably a more efficient way of doing it as it does not count all of the rows.
|||
I would add the following at the top:
IF @.@.ROWCOUNT = 0
RETURN
That will prevent the TRIGGER having to check the inserted and deleted tables needlessly when the TRIGGER is fired but there is no data available. (That can happen in a number of situations, such as CONSTRAINT failure.
Personally, I consider every little piece of code to require 'clock ticks', and I want my code to execute with the minimum 'clock ticks'. That is the attitude required in order to maximize performance efficency. In checking the count of inserted/deleted, you are wasting clock ticks.
It appears that all you are doing is making copies of inserted and deleted ino the Audit table. Why bother with the checks? Just INSERT into Audit from deleted, and then INSERT into Audit from inserted. If the virtual tables are empty, nothing happens -the results are the same with a lot less code (and wasted clock ticks.)
|||
Hi Preet,
You do not need to count rows in order to prove existence. how long it will take if you insert a mass of rows, better to use operator EXISTS.
if exists(select * from inserted) and exists(select * from deleted)
...
Also, use the tip given by Arnie, because most of your comparisons will fail if no row was affected by the statement that caused the trigger to be fired.
delete dbo.t1
where 0 = 1;
The trigger will catch:
...
ELSE
IF @.InsCount_T = @.DelCount_T --Update
INSERTINTO dbo.Audit
SELECT*FROM DELETED
and as you can see, the action was a "delete" and not an "update".
AMB
|||
Thanks to Arnie & Hunchback, its always good to 'hear' another viewpoint.
Sorry Arnie, I omitted a field that I am populating in Audit. I am using 0,1,2 to indicate the type of update. So In the case of an update I am executing ;
INSERTINTO dbo.Audit
SELECT*,1FROM DELETED (not SELECT*FROM DELETED as posted previously)
In the case of an Insert ;
INSERTINTO dbo.Audit
SELECT*,0FROM DELETED
and a delete ;
INSERTINTO dbo.Audit
SELECT*,2FROM DELETED
Yes you are right, there wouldn't be much point in the checks.
Hunchback, I didn't realise that triggers fired if no rows were affected. I'll try it out. However if no rows were affected then DELETED would be empty and it then does not matter which line in the trigger is caught because nothing will go into Audit, except for the needless execution. Tell me, does ; exists(select * from inserted) stop as soon as it finds one occurrence or does it actually evaluate the entire content of bracket ? i.e. does it actually select all of the records or does it stop as soon as it can satisfy the IF statement. If the latter is true then yes your statement is more efficient. Otherwise I will be doing Select * several times such as ;
if exists(select * from inserted) and exists(select * from deleted) --Update
if exists(select * from inserted) and not exists(select * from deleted) --Insert
if not exists(select * from inserted) and exists(select * from deleted) --Delete
I think we've kicked this one to death but I will include the use of ROWCOUNT and yes you both are right that extra work is undesirable no matter how small an impact we think it is going to have.
|||
Hi Preet,
It will stop as soon as it finds something. You can assign a value to a variable in case of existece so you do not need to inquiry again.
if @.@.rowcount = 0
return
declare @.i int
declare @.d int
if exists (select * from inserted)
set @.i = 1
else
set @.i = 0
if exists (select * from deleted)
set @.d = 1
else
set @.d = 0
if @.i = 1 and @.d = 1 then -- update
...
if @.i = 1 and @.d = 0 then -- insert
...
if @.i = 0 and @.d = 1 then -- delete
...
AMB
