Oracle allows you to define procedures that are implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against the associated table. These procedures are called database triggers.
Triggers can be defined only on tables, not on views. However, triggers on the base table(s) of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view.
In addition, triggers are commonly used to
automatically generate derived column values
prevent invalid transactions
enforce complex security authorizations
enforce referential integrity across nodes in a distributed database
enforce complex business rules
provide transparent event logging
provide sophisticated auditing
maintain synchronous table replicates
gather statistics on table access
A trigger has three basic parts:
a triggering event or statement
a trigger restriction
a trigger action
Types of Triggers
When a CREATE TRIGGER statement commits()or a ddl statement runs, the compiled PL/SQL code, called P code (for pseudocode), is stored in the database and the source code of a trigger is flushed from the shared pool.
A trigger that fires at row level can access the data in the row that it is processing by using correlation names. The default correlation names are OLD, NEW, and PARENT. To change the correlation names, use the REFERENCING clause of the CREATE TRIGGER statement.
OLD, NEW, and PARENT are also called pseudorecords.
The structure of a pseudorecord is table_name%ROWTYPE, where table_name is the name of the table on which the trigger is created (for OLD and NEW) or the name of the parent table (for PARENT).
The restrictions on pseudorecords are:
-A pseudorecord cannot appear in a record-level operation. For example, the trigger cannot include this statement:
:NEW := NULL;
-The trigger cannot change OLD field values.
-If the triggering statement is DELETE, then the trigger cannot change NEW field values. Because there is no New field value.
-An AFTER trigger cannot change NEW field values, because the triggering statement runs before the trigger fires.
A BEFORE trigger can change NEW field values before a triggering INSERT or UPDATE statement puts them in the table.
If a statement triggers both a BEFORE trigger and an AFTER trigger, and the BEFORE trigger changes a NEW field value, then the AFTER trigger “sees” that change.
CREATE OR REPLACE TRIGGER t
UPDATE OF salary, department_id OR
WHEN INSERTING THEN
WHEN UPDATING(‘salary’) THEN
WHEN UPDATING(‘department_id’) THEN
DBMS_OUTPUT.PUT_LINE(‘Updating department ID’);
WHEN DELETING THEN
CREATE OR REPLACE TRIGGER log_salary_increase
AFTER UPDATE OF salary ON employees
FOR EACH ROW
INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, ‘New Salary’);
CREATE OR REPLACE TRIGGER print_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
WHEN (NEW.job_id <> ‘AD_PRES’) — do not print information about President
sal_diff := :NEW.salary – :OLD.salary;
DBMS_OUTPUT.PUT(:NEW.last_name || ‘: ‘);
DBMS_OUTPUT.PUT(‘Old salary = ‘ || :OLD.salary || ‘, ‘);
DBMS_OUTPUT.PUT(‘New salary = ‘ || :NEW.salary || ‘, ‘);
DBMS_OUTPUT.PUT_LINE(‘Difference: ‘ || sal_diff);