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 PL/SQL,Visual Basic .NET, Visual C# or Java and stored in the database if so desired and can be executed directly from another tool such as SQL*Plus or called by other procedures or functions or called when a query with an embedded function is run.

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

PL/SQL triggers come in three categories:
  1. DML triggers, attached to tables or views and fired by an event affecting data (insert, delete, update)
  2. DDL triggers which are fired by statements affecting database objects eg. creating or dropping a table
  3. system triggers, attached to schemas or the database itself and fired for example when a user logs on or when the database shuts down

All three categories 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

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


In this example, 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 in this case the trigger 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.

For more help with PL/SQL see our Oracle PL/SQL tutorial. The Oracle PL/SQL Language Reference is also worth reading.

If you're interested in formal Oracle training (either on-line or in a classroom) then see our Oracle training page for more information.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.