CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_name [REFERENCING OLD AS old | NEW AS new] FOR EACH ROW [WHEN condition] PL/SQL block;1、建立BEFORE行触发器
某些情况下使用约束可能无法实现复杂的商业逻辑或企业规则，此时可以考虑使用BEFORE行触发器。下面以确保雇员工资不能低于其原有工资为例，说明建立BEFORE行触发器的方法。
CREATE OR REPLACE TRIGGER tr_emp_sal
BEFORE UPDATE OF sal
ON emp
FOR EACH ROW
BEGIN
IF :NEW.sal < :OLD.sal THEN
RAISE_APPLICATION_ERROR(-20000,‘工资只涨不降‘);
END IF;
END;测试：UPDATE emp SET sal = 800 WHERE empno = 7788;2、建立AFTER行触发器
为了审计DML操作，可以使用语句触发器或Oracle系统提供的审计功能；而为了审计数据变化，则应该使用AFTER行触发器。下面以审计雇员工资变化为例，说明使用AFTER行触发器的方法。在建立触发器之前，首先应建立存放审计数据的表audit_emp_change，示例如下：
CREATE TABLE audit_emp_change( NAME VARCHAR2(10), oldsal NUMBER(6,2), newsal NUMBER(6,2), etime DATE );AFTER行触发器示例
CREATE OR REPLACE TRIGGER tr_sal_change
AFTER UPDATE OF sal
ON emp
FOR EACH ROW
DECLARE
v_temp INTEGER;
BEGIN
SELECT COUNT(*) INTO v_temp FROM audit_emp_change
WHERE NAME = :OLD.ename;
IF v_temp = 0 THEN
INSERT INTO audit_emp_change(NAME,oldsal,newsal,etime)
VALUES (:OLD.ename,:OLD.sal,:NEW.sal,SYSDATE);
ELSE
UPDATE audit_emp_change
SET
oldsal = :OLD.sal,
newsal = :NEW.sal,
etime = SYSDATE
WHERE NAME = :OLD.ename;
END IF;
END;测试：UPDATE emp SET sal = sal * 1.1 WHERE deptno = 20; SELECT * FROM audit_emp_change;3、限制行触发器
当使用行触发器时，默认情况下会在每个被作用行上执行一次触发器代码。为了使得在特定条件下执行行触发器代码，就需要使用WHEN子句对触发条件加以限制。
CREATE OR REPLACE TRIGGER tr_sal_change
AFTER UPDATE OF sal
ON emp
FOR EACH ROW
WHEN (OLD.job=‘CLERK‘)
DECLARE
v_temp INTEGER;
BEGIN
SELECT COUNT(*) INTO v_temp FROM audit_emp_change
WHERE NAME = :OLD.ename;
IF v_temp = 0 THEN
INSERT INTO audit_emp_change(NAME,oldsal,newsal,etime)
VALUES (:OLD.ename,:OLD.sal,:NEW.sal,SYSDATE);
ELSE
UPDATE audit_emp_change
SET
oldsal = :OLD.sal,
newsal = :NEW.sal,
etime = SYSDATE
WHERE NAME = :OLD.ename;
END IF;
END;4、DML触发器使用注意事项当编写DML触发器时，触发器代码不能从触发器所对应的基表中读取数据。
错误示例：
CREATE OR REPLACE TRIGGER tr_emp_sal
BEFORE UPDATE OF sal
ON emp
FOR EACH ROW
DECLARE
maxsal NUMBER(6,2);
BEGIN
SELECT MAX(sal) INTO maxsal FROM emp;
IF :NEW.sal > maxsal THEN
RAISE_APPLICATION_ERROR(-20000,‘超出工资上限‘);
END IF;
END;正确示例：CREATE OR REPLACE PACKAGE pkg_emp
IS
g_maxsal INTEGER;
END pkg_emp;
/
CREATE OR REPLACE TRIGGER tr_emp_sal_row
BEFORE UPDATE OF sal
ON emp
BEGIN
SELECT MAX(sal) INTO pkg_emp.g_maxsal FROM emp;
END;
/
--DML触发器使用注意事项
CREATE OR REPLACE TRIGGER tr_emp_sal
BEFORE UPDATE OF sal
ON emp
FOR EACH ROW
BEGIN
IF :NEW.sal > pkg_emp.g_maxsal THEN
RAISE_APPLICATION_ERROR(-20000,‘超出工资上限‘);
END IF;
END;测试：UPDATE emp SET sal = 3700 WHERE empno = 1111;