LewisC's An Expert's Guide To Oracle Technology
Today I will be writing about triggers. One of the questions I get fairly often is "what is the difference between a function, a procedure and a trigger?" I already wrote about functions and procedures in Learn Oracle: Procedures and Functions. You should probably read that one before you read this post.
What is a trigger?
A trigger is a special case of stored procedure that is fired during an event rather than being explicitly executed. A function or a procedure can be called from a command line or from within a different calling program. A trigger is called automatically when an event is fired.
In Oracle, a trigger can have the procedural code embedded in the body of the trigger, you can call out to another existing function or procedure, or a combination of both. The code in a trigger is generally the same as any procedure or function. You are allowed to create Java triggers but I see this rarely in live systems.
You cannot execute transaction control statements with in a trigger. That means no commits or rollbacks. If you need to rollback a transaction due to logic in a trigger, raise an exception and allow the calling program to take the appropriate action. As for commits, the calling application should always commit or rollback as needed.
Technically, there is a way to execute transaction control within a trigger but that can be abused, and really damage your data integrity, if done poorly. I will leave that topic for a discussion on advanced PL/SQL.
What are trigger events?
Various databases support different triggering events. Most all enterprise class databases support DML triggers (excluding SELECT). That means that the database will optionally fire anytime a table has a row updated, deleted or inserted. Many databases, including Oracle, also support DDL triggers. These fire when you create, alter or drop an object.
Oracle supports many events in addition to DML events. Oracle supports system events such as the database starting or stopping or when there is a server error. Oracle also supports user events such as a user logging on or off.
For a new user or developer, it is important that you understand DML triggers as you will find many applications that are Dependant on triggers. DDL, user and system triggers tend to have more advanced uses although you will see examples of those for various security related processing (such as VPD).
For the purpose of this blog entry, I'll stick to DML triggers.
Trigger Syntax
There are three main types of DML triggers: before, after and instead of. A before or after trigger can be a statement level trigger or a row level trigger.
Before
Obviously, a before statement trigger will fire before a statement is executed. A before row level trigger fires before each row that is affected by a statement.
After
An after statement trigger will fire after a statement is fired. A before row level trigger fires before each row that is affected by a statement.
Statement vs Row
The difference between a statement level trigger and a row level trigger is very easy to understand. If you perform an update that will affect 10 rows, a statement level trigger will fire once and a row level trigger will fire 10 times.
A statement level trigger does not have access to individual column values. A row level trigger does have access to column values. A before row trigger can change new column values.
Instead Of
An instead of trigger is built on a view. Many views are updateable with an instead of trigger. Many more views are not updateable for various reasons. With an instead of trigger, you can update any of these views. As the name implies, an instead of trigger is executed instead of Oracle trying to execute the command directly against the view.
Trigger Syntax
The syntax of a trigger varies database by database but for Oracle and PL/SQL (as well as for EnterpriseDB and SPL), the basic syntax of a DML trigger is:
CREATE OR REPLACE TRIGGER trigger_name
BEFORE|AFTER|INSTEAD OF
INSERT|DELETE|UPDATE ON table_name
[OF column_names]
[REFERENCING [NEW AS new_cols] [OLD AS old_cols]]
[FOR EACH ROW [WHEN (where_condition)]]
[DECLARE]
BEGIN
EXCEPTION
END;
Let's look at the command row by row. Anything within square brackets ([]) is an optional clause. Any items delimited with a pipe (|) is part of a list.
Row 1: The create or replace should seem familiar if you are familiar with procedures and functions. Naming of a trigger follows standard naming conventions and rules.
Row 2: The BEFORE|AFTER|INSTEAD OF clause tells the database what type of trigger it will be.
Row 3: This line allows you define if the triggering event will be on UPDATE, DELETE, INSERT, a combination or all of these. The "ON table_name" identifies the table for which the trigger will be created.
Row 4: An optional clause is the "OF column_names". This statement only affects an UPDATE. If you only want to fire an update trigger when certain columns are modified, you may include this optional statement and list the columns that should be monitored.
Row 5: In a row level trigger, you have the values of the columns as they existed before the command and the new values as a result of the command. For an insert, there are no old values and for a delete, there are no new values. In an update, you have both old and new.
The REFERENCING clause allows you to name the old row and the new row. By default, they are named :OLD and :NEW. You refer to them as :OLD.column_name and :NEW.column_name. You can query them in both before and after row triggers, like:
IF :old.gl_id = :new.gl_id THEN...
In a before row trigger, you can even set the values of :new columns, i.e.
:new.gl_id := sequence.nextval();
Statement level triggers cannot access row level column data.
Row 6: A DML trigger, by default, is a statement level trigger. When you use "FOR EACH ROW", you turn it into a row level trigger.
The WHEN clause allows you to introduce additional matching criteria that Oracle will test before executing a trigger. It is standard where clause type criteria,
FOR EACH ROW WHEN (old.order_date BETWEEN sysdate - 10 and sysdate)
Row 7 and on are standard PL/SQL syntax. Think of them as an anonymous block attached to the trigger name and criteria.
Example
Here is a very simple trigger using the Employees table from Oracle XE.
SQL> desc employees
Name Null? Type
----------------------------------------- -------- ------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
Now, I'll create the trigger on employees. The trigger will only fire on insert or update (not delete), update will only fire when email or hire_date is modified and the trigger body will only fire when the :old.salary is greater than 7500. Because it will only fire when an :old value is set, it will never fire for an insert, even though I am defining it for insert.
SQL> create or replace trigger emp_email
2 before insert or update of email, hire_date
3 on employees
4 for each row when (old.salary > 7500)
5 begin
6 dbms_output.put_line('Trigger executing');
7 end;
8 /
Trigger created.
Next, I'll fire two inserts to add a couple of records. Only one of the records will have a salary > 7500. After the inserts, I will update each record. Only the record with a salary > 7500 will display the trigger message.
SQL> set serveroutput on
SQL> insert into employees
2 (employee_id, last_name, email,
3 hire_date, job_id, salary)
4 values
5 (101010, 'Smith', 'smith@barney.com',
6 sysdate, 'PR_REP', 5000);
1 row created.
SQL> insert into employees
2 (employee_id, last_name, email,
3 hire_date, job_id, salary)
4 values
5 (101011, 'Jones', 'jones@barney.com',
6 sysdate, 'PR_REP', 8000);
1 row created.
SQL> commit;
Commit complete.
SQL> update employees set
2 email = 'under7500@noexec.com'
3 where employee_id = 101010;
1 row updated.
SQL> update employees set
2 email = 'over7500@willexec.com'
3 where employee_id = 101011;
Trigger executing
1 row updated.
SQL> commit;
Commit complete.
SQL>
That's pretty much it for DML triggers.
LewisC
