半夏微凉

半夏微凉

十、触发器——保证数据的正确性

1.触发器触发条件。

十、触发器——保证数据的正确性 数据库 第1张

2.触发器的作用

十、触发器——保证数据的正确性 数据库 第2张

3.触发器的类型。

十、触发器——保证数据的正确性 数据库 第3张

4.触发器的语法。

十、触发器——保证数据的正确性 数据库 第4张

十、触发器——保证数据的正确性 数据库 第5张


5.创建触发器。

CREATE OR REPLACE TRIGGER tri_first

AFTER DELETE

ON

t_procedure

BEGIN

  IF deleting THEN

    dbms_output.put_line('我的第一个触发器');

END IF;

END;

/

SQL> set serveroutput on

SQL> delete from t_procedure where id=5;

我的第一个触发器

1 row deleted

SQL> select * from T_PROCEDURE;

ID         NAME       SHULIANG   TYPE            JIAGE TYPE_ID

---------- ---------- ---------- ---------- ---------- ----------

6          双人床     3          家具             1000 2

2          苹果       100        水果                  1

1          芒果       100        水果                  1

4          小米4      50         电子             1499 3

3          mini平板   20         电子             2000 3

6.查看触发器。

SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='TRIGGER';

SELECT * FROM USER_SOURCE WHERE NAME='TRIGGER_NAME' ORDER BY LINE;

7.DML类型触发器。

例1到5都是行级触发器。

例1:

----创建日志表

CREATE TABLE log_cfq

(ID VARCHAR2(1000),

oper_table VARCHAR2(100),

oper_table_prk VARCHAR2(100),

oper_kd VARCHAR2(100),

oper_date DATE,

CONSTRAINT log_cfq_prk PRIMARY KEY (ID));

-----创建序列号

CREATE SEQUENCE log_cfq_id

MINVALUE 1000000000

MAXVALUE 9999999999

START WITH 1000000000

INCREMENT BY 1

/

-----日志表记录插入

CREATE OR REPLACE TRIGGER tri_dml

BEFORE INSERT

ON productinfo

FOR EACH ROW

BEGIN

  IF inserting THEN

    INSERT INTO log_cfq VALUES

    (log_cfq_id.nextval,

    'productinfo',

    :NEW.productid,

    'insert',

    SYSDATE

    );

    dbms_output.put_line('插入的数据是'||:new.productid);

  END IF;

END;

/

-------------------------------------------------------------

SQL> set serveroutput on

SQL> insert into productinfo values('0240090002','触发器测试',1000,0,0000000001,'','测试1');

插入的数据是0240090002

1 row inserted

例2:在触发器中使用多种触发类型

CREATE OR REPLACE TRIGGER tri_dml_2

AFTER INSERT OR UPDATE OR DELETE

ON productinfo

FOR EACH ROW

BEGIN

  CASE

  WHEN inserting THEN

    INSERT INTO log_cfq VALUES(log_cfq_id.nextval,'productinfo',:new.productid,'insert',SYSDATE);

    dbms_output.put_line('已插入数据,新数据的id主键为:'||:new.productid);

  WHEN UPDATing THEN

    INSERT INTO log_cfq VALUES(log_cfq_id.nextval,'productinfo',:old.productid,'update',SYSDATE);

    dbms_output.put_line('已更新数据,数据的id主键为:'||:old.productid);

  WHEN deleting THEN

    INSERT INTO log_cfq VALUES(log_cfq_id.nextval,'productinfo',:old.productid,'delete',SYSDATE);

    dbms_output.put_line('已删除数据,删除数据的id主键为:'||:old.productid);

  END CASE;

END;

/

SQL> update productinfo set productid='0240090003' where productid='0240090001';

已更新数据,数据的id主键为:0240090001

1 row updated

例3.在触发器中使用if语句。

CREATE OR REPLACE TRIGGER tri_if

BEFORE UPDATE OF productprice

ON productinfo

FOR EACH ROW

BEGIN

  IF (to_char(SYSDATE,'dd')=6 AND :old.productprice>1000) THEN

    raise_application_error(-20000,'今天是6号,不允许修改价格高于1000的数据'); ----只能用20000到30000之间的数值

  INSERT INTO log_cfq VALUES(Log_Cfg_Id.Nextval,'productinfo',:new.productid,'insert',SYSDATE);

    dbms_output.put_line('已经修改数据,插入数据的主键id:'||:new.productid);

  END IF;

END;

/

SQL> update productinfo set productprice='3000' where productid='0240090003';

update productinfo set productprice='3000' where productid='0240090003'

ORA-20000: 今天是6号,不允许修改价格高于1000的数据

ORA-06512: 在 "lentim.TRI_IF", line 3

ORA-04088: 触发器 'LENTIM.TRI_IF' 执行过程中出错

例4.使用when限制语句。

CREATE OR REPLACE TRIGGER tri_when

BEFORE INSERT

ON productinfo

FOR EACH ROW

    WHEN (new.category='0100010001')

  BEGIN

    dbms_output.put_line('输入的原价格为:'||:new.productprice);

    :new.productprice := :new.productprice*0.9;

    dbms_output.put_line('打9折后的价格为'||:new.productprice);

END;

/

SQL> INSERT INTO productinfo VALUES('0240010001','天堂伞测试','59.00','50','0100010001','','测试');

输入的原价格为:59

打9折后的价格为53.1

例5.实现级联修改的触发器。

CREATE OR REPLACE TRIGGER tri_jilian

BEFORE UPDATE OF categoryid ON categoryinfo

FOR EACH ROW

  BEGIN

--更新产品表的产品类型id;

    UPDATE productinfo SET productinfo.category=:new.categoryid WHERE category=:OLD.categoryid;

--把产品类型表的更改写入到log表中;

    INSERT INTO categroyinfo_log VALUES(:old.categoryid,:old.categoryname);

    dbms_output.put_line('已更新产品类型,并保存记录到categroyinfo_log,旧记录的id为:'||:old.categoryid);

END;

/

SQL> update categoryinfo set categoryid='0100010002' where categoryid='0100010001';

已更新产品类型,并保存记录到categroyinfo_log,旧记录的id为:0100010001

1 row updated

SQL> select * from produceinfo;

CATEGROYID CATEGROYNAME

---------- --------------------

0100010001 雨具

SQL> select * from productinfo;

PRODUCTID  PRODUCTNAME          PRODUCTPRICE    QUANTITY CATEGORY   DESPERATION          ORIGIN

---------- -------------------- ------------ ----------- ---------- ------------------------------------------- ----------

0240010001 天堂伞                      59.00          50 0100010002                                                                       杭州

0240040001 夏普LGD-45G100A           7000.00          20 0100030001                                                          日本

0240030001 海尔XQB50-919A            1100.00          29 0100030002                                                          中国

0240030002 三星WF-R1065S/XSC         3600.00          12 0100030002                                                       中国

0240030003 三星XQB55-T86A/XSC        2500.00         111 0100030002                                                     中国

0240050001 台电C436T                  400.00         129 0100040001                                                                中国

0240020001 思科RV082路由器           2800.00          22 0100020001                                                           中国

0240010001 天堂伞测试                  53.10          50 0100010002                                                                   测试

8 rows selected

例6.语句级触发器。

CREATE OR REPLACE TRIGGER TRI_YUJU

  BEFORE INSERT OR UPDATE OR DELETE ON PRODUCTINFO

BEGIN

  IF (TO_CHAR(SYSDATE, 'dd') = 6 AND TO_CHAR(SYSDATE, 'hh24') > 13) THEN

    RAISE_APPLICATION_ERROR(-20001, '当前时间段不允许修改数据');

  END IF;

END;

/

SQL> update productinfo set productinfo.productprice='100' where productinfo.productname='雨伞';

update productinfo set productinfo.productprice='100' where productinfo.productname='雨伞'

ORA-20001: 当前时间段不允许修改数据

ORA-06512: 在 "LENTIM.TRI_YUJU", line 3

ORA-04088: 触发器 'LENTIM.TRI_YUJU' 执行过程中出错

8.触发器的执行顺序。(oracle11g及以上才有的特性,同类型同事件的触发器在10G前都是随机执行的。11g可以用follows来控制)

1.前语句级---2.前行级----3.后行级----4.后语句级

例:

CREATE OR REPLACE TRIGGER TRI_YUJU

  BEFORE INSERT OR UPDATE OR DELETE ON PRODUCTINFO

  FOLLOW TRIGGER_NAME    ----FOLLOW触发器表示限定跟随在这个触发器后面触发。

BEGIN

  IF (TO_CHAR(SYSDATE, 'dd') = 6 AND TO_CHAR(SYSDATE, 'hh24') > 13) THEN

    RAISE_APPLICATION_ERROR(-20001, '当前时间段不允许修改数据');

  END IF;

END;

/


9.复合类型触发器。(compound triggers)

组合触发器指南

a- 组合触发器合并了所有的定时触发器

b- 组合触发器仅限执行 DML 操作,不支持 DDL 和系统操作

c- 你只可在行级代码块中使用 :OLD 和 :NEW 变量标识符 (BEFORE EACH ROW, AFTER EACH ROW) 

d- 不支持 PRAGMA_AUTONOMOUS_TRANSACTION

e- 你可使用 WHEN 语句来提升触发器性能,但组合触发器不支持

f- 所有的定时触发器共享一个声明部分中的变量,直到事务结束。

g- 允许在组合触发器中重复执行某定时触发器

h- INSERTING, UPDATING 和 DELETING 谓词在组合触发器中仍然有效

i- 解决变异表错误 Resolve mutating table error (ORA-04091)

组合触发器带来的好处:

1- 在一个地方实现事务(?) 

2- 所有触发器可共享声明段 

3- 维护单元 

4- 解决变异表错误 (ORA-04091)


复合类型属oracle11g新特性,是触发器的增强部分。包含下面4个类型的触发类型。

语句之前:before statement

行之前:before row

行之后:after row

语句之后:after statement

例:

利用该类型触发器可以方便地解决ora-04091错误。触发器不能对正在被dml操作修改的表进行操作。

CREATE OR REPLACE TRIGGER tri_fuhe_error

BEFORE UPDATE ON t_avg

FOR EACH ROW

  DECLARE

  avg_1 NUMBER(10,2):=0.0;

  BEGIN

    SELECT avg(jiage) INTO avg_1 FROM t_avg WHERE jiage<45;

    IF :new.jiage-:old.jiage>avg_1*0.2 THEN

      dbms_output.put_line('操作的数据有问题!');

    END IF;

END;

SQL> UPDATE t_avg SET jiage=jiage+30 WHERE ID=3;

UPDATE t_avg SET jiage=jiage+30 WHERE ID=3

ORA-04091: 表 LENTIM.T_AVG 发生了变化, 触发器/函数不能读它

ORA-06512: 在 "LENTIM.TRI_FUHE_ERROR", line 4

ORA-04088: 触发器 'LENTIM.TRI_FUHE_ERROR' 执行过程中出错

-------------------------------------------------

CREATE OR REPLACE TRIGGER tir_fuhe

 FOR UPDATE ON t_avg COMPOUND TRIGGER  ----注意for update compound trigger的运用。

  avg_2 NUMBER(10,2):=0.0;

  BEFORE STATEMENT IS

  BEGIN

    SELECT AVG(jiage) INTO avg_2 FROM t_avg WHERE jiage<45;

  END BEFORE STATEMENT;

  AFTER EACH ROW IS

  BEGIN

    IF :new.jiage-:old.jiage>avg_2*0.2 THEN

      raise_application_error(-20002,'操作的数据有问题!');

    END IF;

  END AFTER EACH ROW;

END;

/

SQL> UPDATE t_avg SET jiage=jiage+30 WHERE ID=3;

UPDATE t_avg SET jiage=jiage+30 WHERE ID=3

ORA-20002: 操作的数据有问题!

ORA-06512: 在 "LENTIM.TIR_FUHE", line 10

ORA-04088: 触发器 'LENTIM.TIR_FUHE' 执行过程中出错

10.利用instead of 解决视图更新不了的问题。

--创建测试视图

CREATE OR REPLACE VIEW view_int AS

 SELECT DISTINCT t.id,t.name,t.shuliang,t.type,t.jiage FROM t_avg t ORDER BY t.id;

--创建触发器执行视图更新

CREATE OR REPLACE TRIGGER tri_int

INSTEAD OF INSERT ON view_int

DECLARE

  v_typeid VARCHAR2(10);

BEGIN

  SELECT typeid INTO v_typeid FROM t_type  WHERE typename=:NEW.type;

  INSERT INTO t_avg VALUES(:new.id,:new.name,:new.shuliang,v_typeid,:new.jiage);

END;

/

--验证

SQL>  INSERT INTO view_int VALUES('3','荔枝','50','水果','5');

1 row inserted

11.DDL触发器。

CREATE OR REPLACE TRIGGER TRI_DDL

  BEFORE CREATE OR ALTER OR DROP OR RENAME ON LENTIM.SCHEMA

BEGIN

  IF SYSEVENT = 'CREATE' THEN

    DBMS_OUTPUT.PUT_LINE(DICTIONARY_OBJ_NAME || '正在新增中。。。');

  ELSIF SYSEVENT = 'DROP' THEN

    RAISE_APPLICATION_ERROR(-20000, '不允许删除表');

  ELSIF SYSEVENT = 'ALTER' THEN

    IF DICTIONARY_OBJ_NAME = 'T_AVG' THEN

      RAISE_APPLICATION_ERROR(-20000, '不允许修改t_avg表');

    END IF;

  ELSIF SYSEVENT = 'RENAME' THEN

    RAISE_APPLICATION_ERROR(-20000, '不允许修改表名');

  END IF;

END;

/

--验证:

SQL> drop table t_avg_2;

drop table t_avg_2

ORA-00604: 递归 SQL 级别 1 出现错误

ORA-20000: 不允许删除表

ORA-06512: 在 line 5

SQL> alter table t_avg add test4 varchar2(10);

alter table t_avg add test4 varchar2(10)

ORA-00604: 递归 SQL 级别 1 出现错误

ORA-20000: 不允许修改t_avg表

ORA-06512: 在 line 8

12.用户和系统事件触发器。

--创建log表。

CREATE TABLE t_log_user (

logonid VARCHAR2(10),

logonname VARCHAR2(10),

logontime DATE,CONSTRAINT pri_logonid PRIMARY KEY(logonid));

--创建序列

CREATE SEQUENCE s_log

START WITH 1

INCREMENT BY 1

/

--创建触发器

CREATE OR REPLACE TRIGGER tri_log_time

AFTER logon ON DATABASE

BEGIN

  INSERT INTO t_log_user VALUES(s_log.nextval,sys.Login_User,SYSDATE);

END;

/

--验证

SQL> select * from t_log_user;

LOGONID    LOGONNAME  LOGONTIME

---------- ---------- -----------

1          LENTIM     15/9/9 下午 3

2          LENTIM     15/9/9 下午 3

评论回复


·