How to determine what event has caused Trigger to fire

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