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