prevent trigger fire if caused by cascading

[Warwick32]

I've been reviewing a lot of data auding options, and it seems that generic auditing triggers are mostly horrendous to read/understand/maintain and/or have performance problems.

I've nearly finished developing a generic stored proceedure that can add a table specific trigger to each table, for all columns. This has the advantage that the code that generates the trigger is generic, but the trigger itself is specific, and hence is high performance. It can also be re-run periodically if the table schema changes. I will post it here when done.

However, I don't want to log updates (or deletes, but thats another topic) where they are caused by cascading from a parent table, and am having trouble identifying these. It's not a "recursive_trigger" issue as the update (or delete) occurs as a result of a cascade (recursive triggers are off anyway).

Any advise on how to exit a trigger if it is the result of a cascade would be much appreciated.

Warwick

 

[markros]

Take a look at this article

http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AuditOrBypassTriggerExecution

It seems like using context_info is the only way to figure out that procedure fired the trigger. Of course, it means you will need to implement on the SP or application level.

PluralSight Learning Library

 

[Warwick32]

Hello,

Thankyou for that suggestion. It looked promising in that I thought I could flag a value when the trigger on the parent table fired, and check it on the child table trigger, however it turns out that the child table triggers (update, delete) get triggered first! Therefore I still can't identify if they have cascaded.

I can't rely on flagging it in a stored proceedure as some apps interact with the tables via insert, update statements.

Many thanks anyway, but I'm still looking.

Warwick

 

[markros]

They are triggered first because we need to delete records from child tables first, then parent.

I don't see a solution here without using the application level flag.

PluralSight Learning Library