PL/SQL_触发器2（行触发器）

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;

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;

CREATE TABLE audit_emp_change(
  NAME VARCHAR2(10),
  oldsal NUMBER(6,2),
  newsal NUMBER(6,2),
  etime DATE
);

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;

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;

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;

PL/SQL_触发器2（行触发器）,布布扣,bubuko.com

PL/SQL_触发器2（行触发器）

原文：http://blog.csdn.net/com185272358/article/details/22097737