Not everyone uses SQL triggers today. I’m a huge trigger fan. It allows you to implement business logic and rules at the appropriate model level in the application. Some rules should be client side, other rules should always be database side. Its more efficient, not to mention faster. I’ll put some more notes up soon about how denormalizing your physical schema can lead to massive gains in web tier productivity. But on with the topic de jour…
If you have triggers in your database, you’ll know that 99% of the time the entire trigger code should always, always, be enforced. that’s the point of the trigger. The application cannot get around it – that’s why we put those hard and fast rules in the triggers and constraints. Integrity is a beautiful thing.
However, as with every rule, there is always an exception! One likely exception that I occasionally hit is when doing a system upgrade, or other IT driven update to records, I do NOT want to disrupt the business logic and mess up the last modified dates, or other triggered logic columns. I probably want to “fix” a couple of columns in every row, or a given set of rows for some reason.
In this Use Case, my goal is to have the update fire, have the trigger NOT fire, and thus my fix gets applied, without disrupting the normal integrity of the data.
So how can we do this? There is no way to pass a parameter to a trigger on an adhoc basis! Sure, you can DROP the trigger, do your fix, then reload the trigger, but because that affects every user of the system, you now need full system down/maintenance window with all the inconvenience that entails! Whether you are MySQL, SQLServer, Oracle, DB2… I’ve done them all… there is no way. You cannot ‘drop’ a trigger for just one user. Unless you make one!
Let’s say you have a table “subscriber”, and you have a before insert and before update trigger that sets the create_datetime and last_modified_datetime each time a new row is added or updated. Pretty standard trigger stuff.
And you need to “fix” another column’s value without the create/update date fields getting updated.
Step 1: create a new column in your table definition called “operation_name”. Make it nullable. and typically, I make it varchar(30) or similar. think of this as a transient field. It has no business value.
In your application code, or SQL statements, you can now set the operation_name to any value you like. For example, “NO TRIGGER”.
In your SQL Trigger, which will fire in a discrete transaction, you can now see the “new” value of field operation_name and can act accordingly. In my example I want to skip the entire trigger.
In MySQL, the elegant way to skip the trigger from any point within it is to use the LEAVE statement.
It seems not a lot of people know how to use labels and LEAVE in SQL procedures and triggers, but they are incredibly useful for assisting in structuring maintainable SQL.
Normally, in a trigger you’d code something like…
CREATE trigger bi_subscriber before insert on subscriber for each row
begin
set new.create_date = NOW();
set new.update_date = NOW();
/* ipse lorem... more code... */
end
$$
If however you “label” your begin/end statements, you can do a wondrous thing… you can leave that named block of code!
CREATE trigger bi_subscriber before insert on subscriber for each row
thetrigger: begin
if new.operation_name = "NO TRIGGER" then
LEAVE thetrigger;
end if;
set new.create_date = NOW();
set new.update_date = NOW();
/* ipse lorem... more code... */
end
$$
Now isn’t that a wonderful thing? Obviously you can now use the operation_name to influence trigger behavior instead of just leaving the trigger altogether. Now the client application can dynamically change the way the trigger rules fire. Very cool.
And because MySQL InnoDB is transaction oriented, users cannot clash on these things any more than they can when both trying to update the same row. Each update runs as its own discrete operation. The trigger is still fully active for all other users and insert/updates, just for this one transaction, the trigger knows to exit and do nothing. the nice thing being that this approach works from command line SQL tools too! So for those oddball DBA “fix” jobs it’s ideal.
This example is for MySQL, but the same thing applies to SQLServer and Oracle and most other SQL platforms.
I started doing this trick back in 1992 with SQLServer! Oracle didn’t get triggers that were worth anything until later in the 90’s.
Hope you like it.