What's the difference between a trigger and a procedure in Oracle PL/SQL?

To answer this we need to start with the definitions - a procedure (or function) in Oracle may be written in either PL/SQL or Java and stored in the database if so desired and can be executed directly from another tool such as SQL*Plus or may be called by other procedures or functions or called when a query with an embedded function is run.

Triggers on the other hand are always written in PL/SQL and are (usually) small pieces of code that are run automatically by the Oracle database when a specific event (the triggering event) occurs. Triggers can call PL/SQL stored procedures or functions but can't be called directly by procedures or functions.

PL/SQL triggers come in two types:
DML triggers, attached to tables or views
system triggers, attached to schemas or the database itself

Both types of trigger can be set to fire either just before or just after the triggering event occurs. For example if we look at a DML (data manipulation language) trigger, it can be set to fire either either just before or just after an update, delete or insert operation an a table occurs.

CREATE OR REPLACE TRIGGER update_job_history
AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN

add_job_history(
:old.employee_id, :old.hire_date, :sysdate, :old.job_id, :old.department_id);

END;

In this example trigger, the code is only executed when an update to either the job_id or department_id columns in the employee table occurs. When this happens, the trigger fires and calls a procedure add_job_history passing in the old values (i.e. the values before they were updated) of the columns in the employee record.

Another important point to note is that the trigger, in this case, is fired once for each row that is updated. If the keywords FOR EACH ROW were not present, then the trigger would only be fired once no matter how many rows were updated.

DML triggers are often used to capture the history of changes to a particular table. Other uses for triggers might be to allow changes to data only in certain circumstances (location, time etc) or to record when specific events occur.

Posted in: What's the difference between a trigger and a procedure in Oracle PL/SQL?