第三十一章——数据库触发器以及数据备份实例

数据库的触发器主要用于强制服从复杂的业务规则或要求：

--触发器
/*
  insert 
  delete
  update
  
  类型:两种
    一种叫行级触发器
          表级触发器
          
  update t_student s set s.sname='1111' where s.sno=11111
*/
CREATE OR REPLACE TRIGGER tg_test
AFTER INSERT
ON t_student
BEGIN
  dbms_output.put_line('插入了一条数据 !');
END;


CREATE OR REPLACE TRIGGER tg_test_row
AFTER UPDATE
ON t_student
BEGIN
  dbms_output.put_line('插入了一条数据 !');
END;
/*
  to_char(sysdate, 'day') not in ('星期六','星期日')
  to_char(SYSDATE, 'hh24') between 10 and 17
*/
CREATE OR REPLACE TRIGGER checkforinsert
BEFORE INSERT 
ON t_student

BEGIN
  IF NOT (to_char(sysdate, 'day') not in ('星期六','星期日') AND to_char(SYSDATE, 'hh24') between 10 and 17) THEN
    raise_application_error(-20001, '非工作时间不能添加数据 !');
  END IF
END;

用触发器可以做一个数据备份：

  --CREATE TABLE t_student_back AS SELECT * FROM t_student

  CREATE OR REPLACE TRIGGER T_STU_BACK
  AFTER INSERT OR UPDATE OR DELETE ON T_STUDENT
  FOR EACH ROW
/* :old    :new  */
BEGIN
  IF INSERTING THEN
    INSERT INTO T_STUDENT_BACK
    VALUES
      (:NEW.SNO,
       :NEW.SNAME,
       :NEW.SAGE,
       :NEW.SSEX,
       :NEW.SBIRTHDAY,
       :NEW.IDNUM);
  ELSIF UPDATING THEN
    UPDATE T_STUDENT_BACK S2
       SET S2.SNAME     = :NEW.SNAME,
           S2.SAGE      = :NEW.SAGE,
           S2.SSEX      = :NEW.SSEX,
           S2.SBIRTHDAY = :NEW.SBIRTHDAY,
           S2.IDNUM     = :NEW.IDNUM
     WHERE S2.SNO = :NEW.SNO;
  ELSE
    DELETE T_STUDENT_BACK S3 WHERE S3.SNO = :OLD.SNO;
  END IF;

END;

：old是操作之前的内容

：new是操作之后的内容