首页 » ORACLE 9i-23c » orcle of column trigger test

orcle of column trigger test

------------------------- orcle of column trigger test-------------

--1 create table

CREATE TABLE test_trigger_ofcol

(
    ID_a number,
    id_b number,
    VALUEa VARCHAR2(30),
    valueb varchar2(30)
);
----2 insert

INSERT INTO test_trigger_ofcol VALUES(11,111,'WANG','hehe');

INSERT INTO test_trigger_ofcol VALUES(22,333,'KKKKK','heihei');

commit;

SELECT * FROM test_trigger_ofcol;

-------------------------------
ID_A	ID_B	VALUEA	VALUEB
11	111	WANG	hehe
22	333	KKKKK	heihei
-----------------------------------
--3create trigger

CREATE OR REPLACE TRIGGER TRI_TEST

BEFORE INSERT OR UPDATE
of ID_A

ON test_trigger_ofcol

FOR EACH ROW

--DECLARE

    --PARGMA AUTONOMOUS_TRANSACTION;--//声明自治事务,如果没有这个自治声明,是不能修改本表数据的,(关键)

BEGIN

IF INSERTING
THEN
:NEW.ID_B:=:NEW.ID_B+4;
END IF;

IF UPDATING
THEN
:NEW.ID_A:=:OLD.ID_A+6;
END IF;

END ;

--4验证INSERT 操作。因为ID_A被插入值,因此触发触发器 IF INSERTING块。执行结果如下。

INSERT INTO test_trigger_ofcol VALUES(1111,1111,'WWWWW','hehehe');

SELECT * FROM test_trigger_ofcol;

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

ID_A	ID_B	VALUEA	VALUEB
11	111	WANG	hehe
22	333	KKKKK	heihei
1111	1115	WWWWW	hehehe

----5 验证 UPDATE操作

----因为我修改的是ID_B字段的值,因此 触发器 并没有被触发。因为我触发器是建立在针对 ID_A字段上的。

update test_trigger_ofcol set id_b=113 where id_a=11; 

SELECT * FROM test_trigger_ofcol;

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

ID_A	ID_B	VALUEA	VALUEB
11	113	WANG	hehe
22	333	KKKKK	heihei
1111	1115	WWWWW	hehehe

-------6 UPDATE 操作 修改ID_A 触发器被触发。

update test_trigger_ofcol set id_a=20 where id_b=333; 

SELECT * FROM test_trigger_ofcol;
--------------------
ID_A	ID_B	VALUEA	VALUEB
11	113	WANG	hehe
28	333	KKKKK	heihei
1111	1115	WWWWW	hehehe

-----结论1:OF COLUMN 触发器不会锁自身表。ID_A原值是 22 虽然我要把ID_A修改=20 但是,触发器被触发,ID_A被修改为了 原值+6 即:22+6=28
-----结论2:本表自治事务无须声明 DECLARE PARGMA AUTONOMOUS_TRANSACTION;

作者: 网友--KK 
打赏

目前这篇文章有4条评论(Rss)评论关闭。

  1. Mercedes Carranzo | #1
    2011-12-21 at 03:58

    I loved up to you’ll receive carried out right here. The caricature is tasteful, your authored subject matter stylish. however, you command get bought an impatience over that you would like be turning in the following. ill indubitably come further earlier again since precisely the same nearly very regularly inside of case you protect this increase.

  2. Namari | #2
    2011-09-03 at 01:46

    Home run! Great slugging with that awnser!

  3. 秋风闲人 | #3
    2011-05-23 at 17:03

    受教了!哈哈!

    • Tasmine | #4
      2011-09-03 at 04:19

      Yeah that’s what I’m tailkng about baby–nice work!