触发器四(学习笔记)

REFERENCING子句:

使用“:new.字段”或者是“:old.字段”标记不清，那么也可以通过REFERENCING子句为这两个标识符设置别名，例如可以将“:new”设置为：emp_new，或者将“:old”设置为：emp_old。

create or replace trigger myempaddsal_trigger
  before update on myemp  
  REFERENCING OLD AS myemp_old NEW AS myemp_new
  for each row
declare
   
begin
  IF ABS((:myemp_new.sal - :myemp_old.SAL) / :myemp_old.SAL) > 0.1 THEN
     raise_application_error(-20005,'工资最大涨幅不能超过10%');
  END IF;
end myempaddsal_trigger;

使用WHEN子句定义触发条件:

在WHEN子句中也可以用":new"和":old"访问修改前后的数据，在WHEN子句中使用new和old时可以不加前面的":"号

示例一、增加员工中判断员工工资是否存在，如果工资为0则报错

--创建触发器
create or replace trigger myemptestadd1
  before insert on myemp  
  for each ROW
     WHEN(new.Sal=0)
declare

begin
  raise_application_error(-20003,:NEW.EMPNO||'的工资为0，不合规定!');
end myemptestadd1;
--执行添加
DECLARE
BEGIN
INSERT INTO myemp(empno,ename,job,mgr,sal,deptno)VALUES(9999,'Bdqn','MNAGER',7788,0,10);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;
--结果
ORA-20003: 9999的工资为0，不合规定!
ORA-06512: 在 "TESTS.MYEMPTESTADD1", line 4
ORA-04088: 触发器 'TESTS.MYEMPTESTADD1' 执行过程中出错

示例二、要求工资只能涨不能降

--创建触发器
create or replace trigger myemptestadd1
  before UPDATE on myemp  
  for each ROW
     WHEN(new.Sal<old.Sal)
declare

begin
  raise_application_error(-20003,:old.EMPNO||'的工资只能涨不能降!');
end myemptestadd1;
--执行错误的更新
DECLARE
BEGIN
 UPDATE myemp SET sal=2000 WHERE empno=7788;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;
--结果
ORA-20003: 7788的工资只能涨不能降!
ORA-06512: 在 "TESTS.MYEMPTESTADD1", line 4
ORA-04088: 触发器 'TESTS.MYEMPTESTADD1' 执行过程中出错

触发器谓词:

在触发器定义中专门提供了三个触发器谓词：INSERTING、UPDATING、DELETING

No.	触发器谓词	描述
1	INSERTING	如果触发语句为INSERT,返回TRUE，否则返回FALSE
2	UPDATING	如果触发语句为UPDATE,返回TRUE，否则返回FALSE
3	DELETING	如果触发语句为DELETE,返回TRUE，否则返回FALSE

 

示例三、使用日志表deptlog表记录相关操作dept表

-创建deptlog表
CREATE TABLE deptlog(
             logid        NUMBER,
             TYPE        VARCHAR2(20)       NOT NULL,
             deptno       NUMBER(2),
             logdate      DATE,
             dname        Varchar2(14)      NOT NULL,
             loc          Varchar2(13)      NOT NULL,
             CONSTRAINT pk_logid PRIMARY KEY(logid)
);

--创建序列
CREATE SEQUENCE deptlog_seq;

--创建触发器
create or replace trigger dept_trigger
  before INSERT OR UPDATE OR DELETE on dept  
  for each row
declare
 
BEGIN
   IF inserting THEN
     INSERT INTO deptlog(logid,type,deptno,logdate,dname,loc)
     VALUES(deptlog_seq.nextval,'insert',:new.Deptno,SYSDATE,:NEW.DNAME,:new.Loc);
   ELSIF updating THEN 
      INSERT INTO deptlog(logid,type,deptno,logdate,dname,loc)
     VALUES(deptlog_seq.nextval,'update',:new.Deptno,SYSDATE,:NEW.DNAME,:new.Loc);
   ELSIF deleting THEN
         INSERT INTO deptlog(logid,type,deptno,logdate,dname,loc)
     VALUES(deptlog_seq.nextval,'delete',:old.Deptno,SYSDATE,:old.DNAME,:old.Loc);
   END IF;
  
end dept_trigger;
--测试数据
INSERT INTO dept(deptno,dname,loc)VALUES(87,'测试','SZ');
INSERT INTO dept(deptno,dname,loc)VALUES(43,'公关','SZ');
UPDATE dept SET dname='拓展部' WHERE deptno=66;
DELETE FROM dept WHERE deptno=87;
commit;
--查询
SELECT * FROM dept;
--查询表
SELECT * FROM deptlog;

使用FOLLOWS子句

为一个表创建了多个触发器，在触发时，是不会按照用户希望的顺序执行触发的，

在FOR EACH ROW后增加FOLLOWS 触发器1名称，表示在 触发器1后触发

 

--创建3个相同的触发器
--触发器1
create or replace trigger dept1
  before INSERT OR UPDATE OR DELETE on dept  
  for each row
declare
  
begin
  dbms_output.put_line('执行第1个触发器 dept1');
end dept1;
--触发器2
create or replace trigger dept2
  before INSERT OR UPDATE OR DELETE on dept  
  for each ROW
  follows dept1
declare
  
begin
  dbms_output.put_line('执行第2个触发器 dept2');
end dept1;
--触发器3
create or replace trigger dept3
  before INSERT OR UPDATE OR DELETE on dept  
  for each ROW
    follows dept2
declare
  
begin
  dbms_output.put_line('执行第3个触发器 dept3');
end dept1;

 

执行更新，删除，添加操作

INSERT INTO dept(deptno,dname,loc)VALUES(87,'测试','SZ');
INSERT INTO dept(deptno,dname,loc)VALUES(43,'公关','SZ');
UPDATE dept SET dname='拓展部' WHERE deptno=66;
DELETE FROM dept WHERE deptno=87;

结果:

执行第1个触发器 dept1
执行第2个触发器 dept2
执行第3个触发器 dept3

按指定顺序触发