欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle基础教程之触发器(DML触发器的作用)

程序员文章站 2023-10-01 14:31:54
oracle基础教程之触发器(DML触发器的作用) 举个例子 --开发示例1 SQL> CREATE OR REPLACE TRIGGER emp_...

oracle基础教程之触发器(DML触发器的作用)

oracle基础教程之触发器(DML触发器的作用)

oracle基础教程之触发器(DML触发器的作用)

oracle基础教程之触发器(DML触发器的作用)

举个例子

--开发示例1

SQL> CREATE OR REPLACE TRIGGER emp_trigger1
  2  BEFORE INSERT OR UPDATE OR DELETE
  3  ON emp
  4  BEGIN
  5     IF to_char(sysdate,'day') IN ('星期六','星期日')THEN
  6        RAISE_APPLICATION_ERROR(-20006,'不能在休息日改变员工信息!');
  7     END IF;
  8  END;
  9  /
Trigger created

SQL> delete from emp where empno = 7788;
delete from emp where empno = 7788
ORA-20006: 不能在休息日改变员工信息!
ORA-06512: 在 "SCOTT.EMP_TRIGGER1", line 3
ORA-04088: 触发器 'SCOTT.EMP_TRIGGER1' 执行过程中出错
--开发示例2

--创建审计表
CREATE TABLE delete_emp_audit(
       name VARCHAR2(10),
       delete_time DATE
);

--创建触发器
CREATE OR REPLACE TRIGGER del_emp_trigger
AFTER DELETE ON emp
FOR EACH ROW
BEGIN
  INSERT INTO delete_emp_audit VALUES(:old.ename,SYSDATE);
END;

--测试
DELETE FROM emp WHERE empno = 7499;

select * from delete_emp_audit;
--开发示例3
SQL> CREATE OR REPLACE TRIGGER tr_check_sal
  2  BEFORE UPDATE OF sal ON emp
  3  FOR EACH ROW
  4  WHEN (new.salold.sal*1.5)
  5  BEGIN
  6       RAISE_APPLICATION_ERROR(-20028,'工资只升不降,并且升幅不能超过50%');
  7  END;
  8  /
Trigger created

SQL> UPDATE emp SET sal = sal*1.8 WHERE empno = 7788;
UPDATE emp SET sal = sal*1.8 WHERE empno = 7788
ORA-20028: 工资只升不降,并且升幅不能超过50%
ORA-06512: 在 "SCOTT.TR_CHECK_SAL", line 2
ORA-04088: 触发器 'SCOTT.TR_CHECK_SAL' 执行过程中出错
--开发示例4
CREATE OR REPLACE TRIGGER upd_cascade_trigger
AFTER UPDATE OF deptno
ON dept
FOR EACH ROW
BEGIN
  UPDATE emp SET deptno = :new.deptno WHERE deptno = :old.deptno;
END;

--测试
UPDATE dept SET deptno = 50 WHERE deptno = 10;

select deptno,ename from emp where deptno = 50;