?> What is Trigger? (Database Definition)

Creating Triggers  «Prev  Next»

Lesson 2 What is a trigger?
Objective Define a trigger.

Oracle Database Trigger Defined

A trigger is a stored sub-program that is similar to a procedure and is automatically executed by the database whenever a specified action occurs.
Unlike a procedure, you cannot explicitly call a trigger and execute it. A trigger is always executed implicitly by the database system according to the action, timing, and scope specified by the trigger.
The timing of a trigger specifies whether the trigger is executed before or after the action.
The action of a trigger specifies activity in the database, such as a user updating one or more rows in a table.
The scope of a trigger specifies whether the trigger fires once per row or once per transaction.
Triggers are commonly used to:

  1. Add a date and time stamp into a column in the row before inserting the row into the table
  2. Verify that the current user is allowed to change the row that was modified, and reject the change if the user is not allowed to make the change
  3. Write audit trail records to a log file every time a new row is added to a particular table
  4. Update a summary row in a different table whenever data changes in the specified table
Most triggers are designed to fire when data is modified, added, or removed from tables.
The next lesson compares trigger attributes to those of procedures.

Database Triggers

A database trigger is a stored subprogram associated with a database table, view, or event. The trigger can be called once, when some event occurs, or many times, once for each row affected by an INSERT, UPDATE, or DELETE statement. The trigger can be called after the event, to record it or take some followup action. Or, the trigger can be called before the event to prevent erroneous operations or fix new data so that it conforms to business rules. In Example 1, the table-level trigger fires whenever salaries in the employees table are updated.
For each update, the trigger writes a record to the emp_audit table.
Example 1 Creating a Database Trigger 
CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
new_sal NUMBER(8,2), old_sal NUMBER(8,2) );
CREATE OR REPLACE TRIGGER audit_sal
AFTER UPDATE OF salary ON employees FOR EACH ROW
BEGIN
-- bind variables are used here for values
INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE,
:new.salary, :old.salary );
END;
/

The executable part of a trigger can contain procedural statements as well as SQL data manipulation statements. Besides table-level triggers, there are instead-of triggers for views and system-event triggers for schemas.