Master Oracle Triggers: Real‑World Examples and Step‑by‑Step Guide

Introduction

The article provides a comprehensive tutorial on Oracle triggers, describing what they are, when they fire, and the difference between statement‑level and row‑level triggers.

Trigger Basics

A trigger automatically executes predefined PL/SQL statements when a specified condition occurs; it cannot be called manually. Statement‑level triggers fire once per triggering statement, while row‑level triggers fire for each affected row.

Syntax

<code>CREATE [OR REPLACE] TRIGGER trigger_name trigger_time trigger_event ON table_name [FOR EACH ROW] BEGIN pl/sql_statements; END;</code>

Where trigger_time is BEFORE or AFTER, and trigger_event can be INSERT, UPDATE, or DELETE.

Example 1 – Prevent Weekend Modifications

<code>CREATE OR REPLACE TRIGGER auth_secure BEFORE INSERT OR UPDATE OR DELETE ON tb_emp BEGIN IF TO_CHAR(SYSDATE,'DY')='星期日' THEN RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表tb_emp'); END IF; END; /</code>

This trigger blocks any DML on tb_emp during Sundays.

Example 2 – Auto‑Increment ID Using a Sequence

First create a test table and a sequence:

<code>CREATE TABLE tab_user( id NUMBER(11) PRIMARY KEY, username VARCHAR2(50), password VARCHAR2(50) ); CREATE SEQUENCE my_seq INCREMENT BY 1 START WITH 1 NOCACHE NOCYCLE; </code>

Then attach a trigger to fetch the next sequence value:

<code>CREATE OR REPLACE TRIGGER my_tgr BEFORE INSERT ON tab_user FOR EACH ROW DECLARE next_id NUMBER; BEGIN SELECT my_seq.NEXTVAL INTO next_id FROM dual; :NEW.id := next_id; END; /</code>

Inserting rows automatically receives sequential IDs.

Example 3 – Log DML Operations to a Separate Table

<code>CREATE TABLE test( t_id NUMBER(4), t_name VARCHAR2(20), t_age NUMBER(2), t_sex CHAR ); CREATE TABLE test_log( l_user VARCHAR2(15), l_type VARCHAR2(15), l_date VARCHAR2(30) ); CREATE OR REPLACE TRIGGER test_trigger AFTER INSERT OR UPDATE OR DELETE ON test DECLARE v_type test_log.l_type%TYPE; BEGIN IF INSERTING THEN v_type := 'INSERT'; ELSIF UPDATING THEN v_type := 'UPDATE'; ELSIF DELETING THEN v_type := 'DELETE'; END IF; INSERT INTO test_log VALUES (USER, v_type, TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')); END; /</code>

After each DML on test, a record is written to test_log.

Example 4 – Maintain Department Salary Summary

<code>CREATE TABLE dept_sal AS SELECT deptno, COUNT(empno) total_emp, SUM(sal) total_sal FROM scott.emp GROUP BY deptno; CREATE OR REPLACE TRIGGER emp_info AFTER INSERT OR UPDATE OR DELETE ON scott.emp DECLARE CURSOR cur_emp IS SELECT deptno, COUNT(empno) total_emp, SUM(sal) total_sal FROM scott.emp GROUP BY deptno; BEGIN DELETE FROM dept_sal; FOR v_emp IN cur_emp LOOP INSERT INTO dept_sal VALUES (v_emp.deptno, v_emp.total_emp, v_emp.total_sal); END LOOP; END; /</code>

The trigger refreshes the summary table whenever emp changes.

Example 5 – Capture Deleted Rows

<code>CREATE TABLE employee( id VARCHAR2(4) PRIMARY KEY, name VARCHAR2(15), age NUMBER(2), sex CHAR ); CREATE TABLE old_employee AS SELECT * FROM employee; CREATE OR REPLACE TRIGGER tig_old_emp AFTER DELETE ON employee FOR EACH ROW BEGIN INSERT INTO old_employee VALUES (:OLD.id, :OLD.name, :OLD.age, :OLD.sex); END; /</code>

Deleted rows are archived in old_employee.

Example 6 – Insert Through a View

<code>CREATE TABLE tab1 (tid NUMBER(4) PRIMARY KEY, tname VARCHAR2(20), tage NUMBER(2)); CREATE TABLE tab2 (tid NUMBER(4), ttel VARCHAR2(15), tadr VARCHAR2(30)); INSERT INTO tab1 VALUES (101,'zhao',22); INSERT INTO tab2 VALUES (101,'13761512841','AnHuiSuZhou'); CREATE OR REPLACE VIEW tab_view AS SELECT tab1.tid, tname, ttel, tadr FROM tab1 JOIN tab2 ON tab1.tid = tab2.tid; CREATE OR REPLACE TRIGGER tab_trigger INSTEAD OF INSERT ON tab_view BEGIN INSERT INTO tab1 (tid, tname) VALUES (:NEW.tid, :NEW.tname); INSERT INTO tab2 (tid, ttel, tadr) VALUES (:NEW.tid, :NEW.ttel, :NEW.tadr); END; /</code>

Now inserting into tab_view populates both underlying tables.

Example 7 – Detect Salary Changes

<code>SET SERVEROUTPUT ON; CREATE OR REPLACE TRIGGER sal_emp BEFORE UPDATE ON emp FOR EACH ROW BEGIN IF :OLD.sal > :NEW.sal THEN DBMS_OUTPUT.PUT_LINE('工资减少'); ELSIF :OLD.sal < :NEW.sal THEN DBMS_OUTPUT.PUT_LINE('工资增加'); ELSE DBMS_OUTPUT.PUT_LINE('工资未作任何变动'); END IF; DBMS_OUTPUT.PUT_LINE('更新前工资 ：' || :OLD.sal); DBMS_OUTPUT.PUT_LINE('更新后工资 ：' || :NEW.sal); END; /</code>

The trigger prints a message indicating whether the salary increased, decreased, or stayed the same.

Example 8 – Log CREATE and DROP Statements

<code>CREATE TABLE log_info( manager_user VARCHAR2(15), manager_date VARCHAR2(15), manager_type VARCHAR2(15), obj_name VARCHAR2(15), obj_type VARCHAR2(15) ); SET SERVEROUTPUT ON; CREATE OR REPLACE TRIGGER trig_log_info AFTER CREATE OR DROP ON SCHEMA BEGIN INSERT INTO log_info VALUES ( USER, SYSDATE, SYS.DICTIONARY_OBJ_NAME, SYS.DICTIONARY_OBJ_OWNER, SYS.DICTIONARY_OBJ_TYPE ); END; /</code>

After any CREATE or DROP in the schema, the operation details are recorded in log_info. The article also shows how to enable/disable triggers with ALTER TRIGGER statements.

Conclusion

The provided examples demonstrate how Oracle triggers can enforce business rules, automate key generation, maintain audit logs, synchronize related tables, and capture schema changes, offering a powerful toolset for database developers.