Trigger

From Oracle FAQ
Jump to: navigation, search

A trigger is a program in a database that gets called each time a row in a table is INSERTED, UPDATED, or DELETED. Triggers allow you to check that any changes are correct, or to fill in missing information before it is COMMITed. Triggers are normally written in PL/SQL or Java.

Examples[edit]

Audit logging:

CREATE TABLE t1 (c1 NUMBER);
CREATE TABLE audit_log(stamp TIMESTAMP, usr VARCHAR2(30), new_val NUMBER);

CREATE TRIGGER t1_trig
  AFTER INSERT ON t1 FOR EACH ROW
BEGIN
  INSERT INTO audit_log VALUES (SYSTIMESTAMP, USER, :NEW.c1);
END;
/

Prevent certain DML operations:

CREATE OR REPLACE TRIGGER t1_trig
  BEFORE INSERT OR UPDATE OR DELETE
  ON t1
BEGIN
  raise_application_error(-20001,'Inserting and updating are not allowed!');
END;
/

Also see[edit]

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #