stopping a trigger making a trigger fire 1

[maclav]

Hi
Sorry to post 2 in quick succession but i am having a bit of trouble.

I have 2 tables
table a has a companies info in
table b has which sheet of the spread sheet the table should appear.

on the spread sheet if the person enters an excisting company onto a new sheet. the trigger allows the insert into table a to go ahead (after trigger) and then does a loud of checks writes to table b and at the end deletes the inserted row from table a.

However i have also a delete trigger which records the deletion of and information from table a.
I dont want the delete trigger to record any deletions done by another trigger.
Is there any way to identify that a deletion was caused by a trigger?

 

[nickdel]

maclay, you could check the nested level before running the trigger. i.e.

if @@nestlevel =0

If the this trigger was called from another then the nested level would be 1 and so on...

Hope this helps

Nick

 

[maclav]

Thanks that perfect.

 

[ESquared]

I don't think using @@nestlevel is safe. @@nestlevel is not just for triggers but for any call like a stored procedure. Assuming that no action will ever be taken on your company table from a stored procedure is in my opinion not a good idea.

In fact, if you're using ADO, your @@nestlevel is always at least 1 anyway because it's submitting most everything using "exec sp_executesql."

Since you are going to allow "bad" data to be entered in your company table, you could add a bit column that indicates whether the row has been vetted as good. Set its default to 0. On insert, if it is not a duplicate, set the column to 1. The delete trigger on this table would ignore deletes to rows that have this column set to 0. An update trigger could prevent updates that set the column from 1 to 0. The insert trigger could then do its work and delete the row without trigger-bouncing.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]