Test Oracle trigger

                   ============创建测试需要的表======================
========创建一个源数据表SRCTB，这个表和表TB1、TB2结构相同，在这个表中存有1，000，000条数据， 创建这个表是为了在测试中向表TB1、TB2中一次性插入1，000，000条数据。
CREATE TABLE SRCTB (ID NUMBER, BIRTHDATE VARCHAR2(30), LIVEAGE NUMBER,NAME VARCHAR2(50));
CREATE UNIQUE INDEX IX1_SRCTB ON SRCTB (ID);
CREATE TABLE TB1 (ID NUMBER, BIRTHDATE VARCHAR2(30), LIVEAGE NUMBER,NAME VARCHAR2(50));
CREATE UNIQUE INDEX IX1_TB1 ON TB1 (ID);
CREATE TABLE TB2 (ID NUMBER, BIRTHDATE VARCHAR2(30), LIVEAGE NUMBER,NAME VARCHAR2(50));
CREATE UNIQUE INDEX IX1_TB2 ON TB2 (ID);
CREATE TABLE LOGTB (ID NUMBER, BIRTHDATE VARCHAR2(30), LIVEAGE NUMBER,NAME VARCHAR2(50));
CREATE UNIQUE INDEX IX1_LOGTB ON LOGTB (ID);
============创建测试需要的Trigger=================
========在表TB1上不定义触发器（TRIGGER）；
========在表TB2上定义3个触发器，这3个触发器分别是插入（INSERT）触发器、删除（DELETE）触发器 和更新(UPDATE)触发器，这些触发器只用来执行一个简单的SQL语句， 把相应的数据存储到一个日志表（LOGTB）中。

CREATE OR REPLACE TRIGGER TESTTRG1
AFTER INSERT ON TB2
FOR EACH ROW
BEGIN
INSERT INTO LOGTB (ID,BIRTHDATE,LIVEAGE,NAME) VALUES (:NEW.ID, :NEW.BIRTHDATE,:NEW.LIVEAGE,:NEW.NAME);
END;
/

CREATE OR REPLACE TRIGGER TESTTRG2
AFTER UPDATE ON TB2
FOR EACH ROW
BEGIN
UPDATE LOGTB SET BIRTHDATE=:NEW.BIRTHDATE,NAME=:NEW.NAME,LIVEAGE=:NEW.LIVEAGE WHERE ID=:OLD.ID;
END;
/

CREATE OR REPLACE TRIGGER TESTTRG3
AFTER DELETE ON TB2
FOR EACH ROW
BEGIN
DELETE FROM LOGTB WHERE ID=:OLD.ID;
END;
/

delete from SRCTB;
delete from TB1;
delete from TB2;

select * from SRCTB;
select * from TB1;
select * from TB2;

insert into SRCTB
select rownum as id,
to_char(sysdate + rownum / 24 / 3600, 'yyyy-mm-dd hh24:mi:ss') as birthdate,
trunc(dbms_random.value(50, 100)) as liveage,
dbms_random.string('x', 20) name
from dual
connect by level <= 100000;

set timing on
insert into TB1 select * from SRCTB;
update TB1 set liveage=100;
delete from TB1;

insert into TB2 select * from SRCTB;
update TB2 set liveage=100;
delete from TB2;




test result
=====================================

insert 10000 rows, old: 00:00:00.06, new: 00:00:00.46
update 10000 rows, old: 00:00:00.02, new: 00:00:00.94
delete 10000 rows, old: 00:00:00.12, new: 00:00:02.51

insert 100000 rows, old: 00:00:01.35, new: 00:00:13.07
update 100000 rows, old: 00:00:03.11, new: 00:00:06.51
delete 100000 rows, old: 00:00:04.34, new: 00:00:14.55





 
 
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/725820/viewspace-2155363/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/725820/viewspace-2155363/