A database trigger is a stored procedure that automatically executes whenever an event occurs. The event may be insert-delete-update operations. For example, Oracle initiates an ‘AFTER INSERT’ trigger after an insert event has occurred or an ‘AFTER UPDATE’ trigger after an update event has occurred.
You can write triggers that fire whenever one of the following operations occurs:
- DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user
- DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database
- Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database
Triggers supplement the standard capabilities of Oracle to provide a highly customized database management system. For example, a trigger can restrict DML operations against a table to those issued during regular business hours. You can also use triggers 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 auditing
- Maintain synchronous table replicates
- Gather statistics on table access
- Modify table data when DML statements are issued against views
- Publish information about database events, user events, and SQL statements to subscribing applications
After Insert Trigger
CREATE or REPLACE TRIGGER emp_after_insert AFTER INSERT ON employees
FOR EACH ROW
DECLARE
BEGIN
insert into emp_backup values (:new.empid, :new.fname, :new.lname);
END;
After Update Trigger
SQL> CREATE or REPLACE TRIGGER emp_after_update AFTER UPDATE OF name ON employees
FOR EACH ROW
DECLARE
BEGIN
update emp_backup
set name = :new.name
where empid = :old.empid;
END;