A table trigger is a really wonderful thing (except when it’s not; be sure to read Tom Kyte’s The Trouble with Triggers).
A trigger executes whenever an action is performed on a table relevant to that trigger. What’s so wonderful? There’s no way to get around it. Doesn’t matter what SQL I execute or where I execute it from (e.g., if you actually did the nasty and are allowing Java developers to execute DML statements).
So if you want to make sure that a value in a column is always upper-case and has no leading or trailing blanks, then you might create a trigger like:
create or replace trigger person_briu before
insert or update on person
for each row
begin
:new.username := trim(upper(:new.username));
end;
And if you added typical auditing columns to your table in an Oracle APEX application, you might want to create a trigger like:
create or replace trigger person_briu before
insert or update on person
for each row
begin
if inserting then
:new.created := current_timestamp;
:new.created_by := coalesce(sys_context('APEX$SESSION', 'APP_USER'), user);
end if;
if updating then
:new.updated := current_timestamp;
:new.updated_by := coalesce(sys_context('APEX$SESSION', 'APP_USER'), user);
end if;
end;
(Note: this is the most crude form of row auditing one can imagine. Check out Connor McDonald’s amazing auditing utility for something a bit more robust.)
Which allows me to immediately point out:
- :new and :old are “pseudo-triggers” that look like they were defined with %rowtype, but are not compatible with variables defined using %rowtype (which means you can’t pass :new as a parameter to a procedure). This is super annoying and hopefully will be fixed before my grandchildren start writing PL/SQL.
- Use inserting, updating and deleting functions (all defined in the dbms_standard package!) to determine what DML action caused the trigger to fire. This script does the demonstrating so I don’t have to.
And also to make the most important point of all:
Start using compound triggers instead of separate row-level and statement-level triggers.
The compound trigger, introduced in Oracle Database 11g, allows you to define one trigger that contains (or can contain) sections for each type of trigger activation (before statement, after row, etc.). This kind of trigger makes it easier to manage your triggers, be sure about trigger execution order, and in particular, is very handy when dealing with mutating table errors.
Check out this compound trigger example on Oracle LiveSQL.
What’s that? You will get a mutating table error when your row-level trigger tries to read or write the table from which the trigger was fired. This is because it’s in the midst of changing (mutating), so the database doesn’t have a consistent state of data in the table to offer to you.
There are a number of ways to get around this, which I talk about in the recording of this tip (and is demonstrated in that LiveSQL script above).
And here’s some advice I hope you follow:
- Minimize the code in a trigger. Anything more complex than an assignment, anything approaching an actual business role, should be moved to a procedure in a package. Just call the procedure and pass it whatever is needed.
- Avoid non-query DML in triggers. That is, you should do everything you can to avoid executing inserts, deletes, and updates in a table trigger. The problem, briefly, is that sometimes Oracle Database will “re-start” the execution of a trigger – that is, the code in your trigger could run more than once. So if you insert a row, you might end up inserting two or three rows – or throwing an exception when you try to insert the second one (unique index). Tom Kyte railed on this extensively. Curious? Start with this AskTOM Q&A.
- Never use an autonomous_transaction pragma to get around problems with making and saving changes in a trigger (that is, you did put non-query DML in your trigger). That’s a clear sign you are headed down a rabbit hole.
- When you are performing bulk DML operations on a table (hopefully by using forall), disable row-level triggers if at all possible. They will slow down the DML operation considerably. This LiveSQL script demonstrates and here’s another script with a PL/SQL procedure you can use to enable/disable via an API.
- Use the trigger when clause to shortcut execution of the trigger body, another performance optimization. Here’s a script that will generate common patterns for this clause.
By the way, there are other types of triggers.
- DDL triggers (you can, for example, stop anyone from dropping a table)
- Login triggers (useful when you need to set context values for VPD, or stop anyone from logging in).
- Instead-of triggers, which can be defined on views so that you can execute DML operations on otherwise not-very-DML-able structures. Darryl Hurley offers an example here.
In closing, I strongly suggest checking out Tim Hall’s excellent pages on triggers here. He even has a handy and intriguing section titled “Should you use triggers at all? (Facts, Thoughts and Opinions)”.
