Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Disable only one trigger action Expand / Collapse
Author
Message
Posted Tuesday, May 6, 2014 6:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:47 AM
Points: 18, Visits: 85
I have a trigger that executes AFTER INSERT, UPDATE, DELETE. Is there a way to disable and then reenable only the "AFTER DELETE", letting AFTER INSERT, UPDATE act normally?
Thank you in advance
Post #1567907
Posted Tuesday, May 6, 2014 7:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 13,082, Visits: 12,538
pgmoschetti (5/6/2014)
I have a trigger that executes AFTER INSERT, UPDATE, DELETE. Is there a way to disable and then reenable only the "AFTER DELETE", letting AFTER INSERT, UPDATE act normally?
Thank you in advance


You can't actually disable it but you could ALTER the trigger to not include DELETE and then alter it again after you are done doing what I assume must be some bulk data deletion.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1567947
Posted Tuesday, May 6, 2014 8:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:47 AM
Points: 18, Visits: 85
You are right, that's a good solution. I've only an additional problem, I must think of: as you said, it is a bulk delete. This bulk delete will be run by a .NET client where the customer has a list of all tables in the database, can select any of them, select a couple of dates and then issue that bulk delete on all the selected tables. Each table has its own trigger and on each table I shall disable that "AFTER DELETE".
I will think on it, taking your suggestion as my starting point,
Thanks, regards
Post #1567981
Posted Tuesday, May 6, 2014 8:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 13,082, Visits: 12,538
pgmoschetti (5/6/2014)
You are right, that's a good solution. I've only an additional problem, I must think of: as you said, it is a bulk delete. This bulk delete will be run by a .NET client where the customer has a list of all tables in the database, can select any of them, select a couple of dates and then issue that bulk delete on all the selected tables. Each table has its own trigger and on each table I shall disable that "AFTER DELETE".
I will think on it, taking your suggestion as my starting point,
Thanks, regards


If this is something is going to happen frequently I would consider splitting your triggers into two. One for Insert, Update and the other for Delete. Then you can just modify your .NET code to first disable the delete trigger, fire your deletes, enable the delete trigger again. I am not a huge fan of triggers in general but if I do use them I like to keep the logic separate for the different types of actions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1567989
Posted Tuesday, May 6, 2014 10:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 10,267, Visits: 13,238
Sean Lange (5/6/2014)
pgmoschetti (5/6/2014)
You are right, that's a good solution. I've only an additional problem, I must think of: as you said, it is a bulk delete. This bulk delete will be run by a .NET client where the customer has a list of all tables in the database, can select any of them, select a couple of dates and then issue that bulk delete on all the selected tables. Each table has its own trigger and on each table I shall disable that "AFTER DELETE".
I will think on it, taking your suggestion as my starting point,
Thanks, regards


If this is something is going to happen frequently I would consider splitting your triggers into two. One for Insert, Update and the other for Delete. Then you can just modify your .NET code to first disable the delete trigger, fire your deletes, enable the delete trigger again. I am not a huge fan of triggers in general but if I do use them I like to keep the logic separate for the different types of actions.


+1 for separating out the triggers based on action.

Another option is to use the APP_NAME() function to skip the delete functionality of your trigger if you are setting the application name attribute of the connection string for the application. This isn't full proof as someone could spoof the application name in the connection string from another app. The code would be something like this:

/* identify deletes */
IF EXISTS(SELECT 1 FROM inserted AS I JOIN deleted D ON I.pk = D.pk)
BEGIN;
IF APP_NAME() = 'BULK DELETE APPLICATION'
BEGIN;
/* do nothing */
RETURN;
END;
ELSE
BEGIN;
/* ON DELETE ACTION */
END;
END;

Also if you are using a application user you could use SYSTEM_USER instead of APP_NAME() to "skip" the delete logic.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1568075
Posted Tuesday, May 6, 2014 2:38 PM This worked for the OP Answer marked as solution
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 2,194, Visits: 3,301
I use CONTEXT_INFO() to control triggers in those cases. If you disable the trigger, of course other deletes also won't process the trigger, when you might have wanted them to. But CONTEXT_INF() is unique to the session or batch.

SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1568193
Posted Tuesday, May 6, 2014 11:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:47 AM
Points: 18, Visits: 85
Thank you all for your interesting suggestions. Maybe the CONTEXT_INFO solution is the best in my case: separate logic for AFTER DELETE is good, but in my case the logic is just the same for all conditions (populating a "bridge" table with changes); app name is interesting, but the same app has a "normal" part which needs triggers and a "maintenance" part with bulk deletes which should disable only the AFTER DELETE (I could change the app name).
Post #1568284
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse