Friday, July 1, 2016

Determining the action in a multi-action trigger

I found a small trigger trick that I haven't seen anywhere else. In the interests of promoting my own re-use of code, I have been trying to create triggers that managed all three actions in one block of code. Those actions are DELETE, UPDATE, and INSERT. Unfortunately, unlike the MERGE statement, there is no "$action" global variable to tell us what action is taking place.
Here is a trigger example of testing for the delete action:
ALTER TRIGGER Loading._Batch_Run_Master_Key_Lock 
   ON  Loading._Batch_Run_Master_Key 
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
 SET NOCOUNT ON;

 -- Block deletion of our modal row.

    IF ((SELECT COUNT(*) FROM inserted)=0)
 BEGIN
  PRINT 'DELETE!'
  ROLLBACK
 END
An example use case is when you are creating a Singleton row in a table. You need to prevent the deletion of that row by accident. Interestingly, the singleton row in Oracle's DUAL table was not protected, and occasionally DBA's and developers have had headaches with this ubiquitously referenced table. This may appear to be overdoing your design, but I see as defining as much as you can in declarative rules.
To detect an update, you can do as follows:

IF (
 (SELECT COUNT(*) FROM inserted)=1 
   AND 
 (SELECT COUNT(*) FROM deleted)=1
   )

WARNING: I am only using this technique with single row commands. In a multi-row update, I don't know what the counts would be. This remains to be tested.
Some may disagree that this is the best way to organize code. They may feel it is better to have three separate triggers, one for each action.
I cannot see any valid argument for this, except that each block of code is simpler, given it does not have to deal with determining what action is taking place. However, I believe the three sets of code to be more like facets of the same problem: How does this table deal with change? As many times as I can recall, my update triggers were closely related to my insert triggers. If I am updating the modified date in the update action, did I set a default modified date on insert? Or if the update action is incrementing a counter, then it is closely related that the insert action or a default constraint set it to 0. I find it difficult to remember the other triggers when I am working on one of the triggers.
And what about the delete trigger? Mostly this is used for auditing. If I add a column to a table, I like to browse all the actions in a single expression or block of code, so that I can examine the new column's relationship with each action.

No comments:

Post a Comment