PL/SQL触发器3（使用DML触发器）

为了确保数据库数据满足特定的商业规则或企业逻辑，可以使用约束、触发器和子程序实现。因为约束性能最好，实现最简单，所以首先约束；如果使用约束不能实现特定规则，那么应该选择触发器；如果触发器仍然不能实现特定规则，那么应该选择子程序（过程和函数）。DML触发器可以用于实现数据安全保护、数据审计、数据完整性、参照完整性、数据复制等功能。
1、控制数据安全
在服务器级别控制数据安全是通过授予和收回对象权限来完成的。

SQL> CONN SCOTT/TIGER;
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON emp TO SMITH;

当用户具有了以上对象权限之后，就可以随时在EMP表上执行相应的SQL操作。 为了实现更复杂的安全模型（例如限制要修改数据、修改时间等），就需要使用DML触发器了。
下面以限制用户在正常工作时间（9:00~17:00）改变EMP表数据为例。

CREATE OR REPLACE TRIGGER tr_emp_time
BEFORE INSERT OR DELETE OR UPDATE
ON emp
BEGIN
  IF to_char(SYSDATE,‘HH24‘) NOT BETWEEN
    ‘9‘ AND ‘17‘ THEN
    RAISE_APPLICATION_ERROR(-20001,‘非工作时间‘);
  END IF;
END;

2、实现数据审计
审计可以用于监视非法和可疑的数据库活动。Oracle数据库本身提供了审计功能。

AUDIT INSERT,DELETE,UPDATE ON emp BY ACCESS;

--查询数据库审计

select a.USERNAME,a.OBJ_NAME,a.ACTION_NAME,a.TIMESTAMP from user_audit_object a;

如上所示，在设置了审计选项之后，如果在EMP表上执行INSERT、UPDATE和DELETE操作，Oracle会将关于SQL操作的信息（用户、时间等）写入到数据字典中。注意，使用数据库审计只能审计SQL操作，而不会记录数据变化。为了审计SQL操作所引起的数据变化，必须要使用DML触发器。

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;

3、实现数据完整性
假定希望雇员的新工资不能低于原工资，但也不能高出原工资的20%，使用约束显然无法实现该规则，但通过触发器却可以实现该项规则。

CREATE OR REPLACE TRIGGER tr_check_sal
BEFORE UPDATE OF sal
ON emp
FOR EACH ROW
BEGIN
  dbms_output.put_line(‘旧工资：‘ || :OLD.sal);
  dbms_output.put_line(‘新工资：‘ || :NEW.sal);
  IF :NEW.sal < :OLD.sal OR :NEW.sal > :OLD.sal * 1.2 THEN
    RAISE_APPLICATION_ERROR(-20000,‘工资只升不降，并且升幅不能超过20%‘);
  END IF;
END;

4、实现参照完整性
参照完整性是指若两个表之间具有主从关系（也即主外键关系），当删除主表数据时，必须确保相关的从表数据已经被删除。为了实现级联删除，可以在定义外部键约束时指定ON DELETE CASCADE关键字。

SQL> ALTER TABLE emp ADD CONSTRAINT fk_deptno
FOREIGN KEY (deptno) REFERENCES dept(deptno)
ON DELETE CASCADE;

为了实现级联更新，可以使用触发器。示例如下：

CREATE OR REPLACE TRIGGER tr_update_cascade
AFTER UPDATE OF deptno
ON dept
FOR EACH ROW
BEGIN
  UPDATE emp SET deptno = :NEW.deptno
  WHERE deptno = :OLD.deptno;
END;

PL/SQL触发器3（使用DML触发器）,古老的榕树,5-wow.com