    最近公司需要根據業務寫一個觸發器，要求當某個使用者新增刪除更新t_prouduct_order_col 表的欄位時，知道是那個資料庫使用者，哪個ip，什麼客戶端，以及更新欄位涉及到的客戶使用者、欄位說明等內容。研究的兩天終於寫出一個觸發器，但是很爛，後來，又重另外一個角度思考，寫出一個比較完美的觸發器。雖然都可以實現相同的效果，但是效率大大的不一樣，所以看問題要從不同的角度去思考去對待問題，世上無難事，只怕有心人！！！

相當爛的觸發器：

--建立表
CREATE TABLE trigger_T1
( "ID_PRODUCT_ORDER" VARCHAR2(50),
"ID_COLUMN" VARCHAR2(50),
"GRANT_TIME" DATE,
"GRANT_TYP" VARCHAR2(100),
"REVOKE_TIME" DATE,
"ID_USER_MODIFY" VARCHAR2(50),
"MODIFY_TIME" DATE
);

CREATE TABLE trigger_T3
( "ID_PRODUCT_ORDER" VARCHAR2(50),
"FULL_NAME" VARCHAR2(100),
"USER_NAME" VARCHAR2(100)
);

CREATE TABLE trigger_T5
( "TIME1" DATE,
"USERNAME" VARCHAR2(100),
"OSUSER" VARCHAR2(100),
"MACHINE" VARCHAR2(100),
"TERMINAL" VARCHAR2(100),
"PROGRAM" VARCHAR2(100),
"PARSING_SCHEMA_NAME" VARCHAR2(100),
"STATUS" VARCHAR2(100),
"CLIENT_IP" VARCHAR2(100),
"ID_PRODUCT_ORDER" VARCHAR2(100),
"ID_COLUMN" VARCHAR2(100),
"USER_NAME" VARCHAR2(100),
"FULL_NAME" VARCHAR2(100),
"OLD_COLUMN" VARCHAR2(20)
);

CREATE TABLE trigger_T4
( "ID_COLUMN" VARCHAR2(50),
"ID_PRODUCT_ORDER" VARCHAR2(50),
"FULL_NAME" VARCHAR2(50),
"USER_NAME" VARCHAR2(50),
"FLAG" VARCHAR2(30),
"TIME1" DATE
) ;

--建立檢視
create or replace view v_id_order_full_user as
select id_product_order,full_name,user_name from (select user_name,id_customer,id_product_order
from t_product_user pu,t_user u where pu.id_user=u.id) a,t_customer cu where a.id_customer=cu.id;

---建立儲存過程

CREATE OR REPLACE PROCEDURE sp_insert(s1 in varchar2, s2 in varchar2) as
begin
insert into t4
select *
from (select distinct id_Column,
id_product_order,
full_name,
user_name,
'gsinfo',
grant_time
from (select distinct id_Column,
id_product_order,
full_name,
user_name,
'gsinfo',
grant_time
from (select x.ID_PRODUCT_ORDER,
ab.FULL_NAME,
USER_NAME,
id_column,
'gsinfo',
grant_time
from t3 ab
join t1 x
on ab.id_product_order = x.id_product_order)
group by id_Column,
id_product_order,
full_name,
user_name,
'gsinfo',
grant_time)
group by id_Column,
id_product_order,
full_name,
user_name,
'gsinfo',
grant_time)
where id_product_order = s1
and id_column = s2;
commit;
end;

--授權檢視系統檢視
grant select on v_$session to gsinfo1;
grant select on v_$sql to gsinfo1;

--建立觸發器
create or replace trigger t_jk_qx111
after INSERT OR UPDATE or DELETE OF id_product_order, id_column on t_product_order_col
For each row
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;

Begin
IF INSERTing THEN
insert into t1
select :new.id_product_order, :new.id_column, sysdate, '', '', '', ''
from t_product_order_col
where rownum = 1;
commit;
insert into t3
select *
from v_id_order_full_user
where id_product_order = :new.id_product_order;
commit;
sp_insert(:new.id_product_order, :new.id_column);

insert into t5
select a.time1,
USERNAME,
OSUSER,
MACHINE,
TERMINAL,
PROGRAM,
PARSING_SCHEMA_NAME,
STATUS,
CLIENT_IP,
b.id_product_order,
b.id_column,
b.user_name,
b.full_name,
''
from (select sysdate time1,
s.SID,
s.SERIAL#,
s.USERNAME,
s.OSUSER,
s.MACHINE,
s.TERMINAL,
s.PROGRAM,
q.PARSING_SCHEMA_NAME,
'INSERT' STATUS,
sys_context('userenv', 'ip_address') CLIENT_IP
from sys.v_$sql q, sys.v_$session s
where s.audsid = (select userenv('SESSIONID') from dual)
and s.prev_sql_addr = q.address
AND s.PREV_HASH_VALUE = q.hash_value) a,
(select *
from t4
where id_product_order = :new.id_product_order
and id_column = :new.id_column) b
where a.PARSING_SCHEMA_NAME = 'GSINFO';
commit;
ELSIF deleting THEN
sp_insert(:new.id_product_order, :new.id_column);
insert into t5
select a.time1,
USERNAME,
OSUSER,
MACHINE,
TERMINAL,
PROGRAM,
PARSING_SCHEMA_NAME,
STATUS,
CLIENT_IP,
b.id_product_order,
b.id_column,
b.user_name,
b.full_name,
''
from (select sysdate time1,
s.SID,
s.SERIAL#,
s.USERNAME,
s.OSUSER,
s.MACHINE,
s.TERMINAL,
s.PROGRAM,
q.PARSING_SCHEMA_NAME,
'DELETE' STATUS,
sys_context('userenv', 'ip_address') CLIENT_IP
from sys.v_$sql q, sys.v_$session s
where s.audsid = (select userenv('SESSIONID') from dual)
and s.prev_sql_addr = q.address
AND s.PREV_HASH_VALUE = q.hash_value) a,
(select *
from t4
where id_product_order = :old.id_product_order
and id_column = :old.id_column) b
where a.PARSING_SCHEMA_NAME = 'GSINFO';
commit;
ELSIF updating THEN
insert into t1
select :new.id_product_order,
:new.id_column,
sysdate,
:old.id_column,
'',
'',
''
from t_product_order_col
where rownum = 1;
commit;
insert into t3
select *
from v_id_order_full_user
where id_product_order = :new.id_product_order;
commit;
sp_insert(:new.id_product_order, :new.id_column);
insert into t5
(user_name,
time1,
USERNAME,
OSUSER,
MACHINE,
TERMINAL,
PROGRAM,
PARSING_SCHEMA_NAME,
STATUS,
CLIENT_IP,
id_product_order,
id_column,
full_name,
OLD_COLUMN)
select distinct user_name,
time1,
USERNAME,
OSUSER,
MACHINE,
TERMINAL,
PROGRAM,
PARSING_SCHEMA_NAME,
STATUS,
CLIENT_IP,
id_product_order,
id_column,
full_name,
:old.id_column
from (select a.time1,
USERNAME,
OSUSER,
MACHINE,
TERMINAL,
PROGRAM,
PARSING_SCHEMA_NAME,
STATUS,
CLIENT_IP,
b.id_product_order,
b.id_column,
b.user_name,
b.full_name,
:old.id_column
from (select sysdate time1,
s.SID,
s.SERIAL#,
s.USERNAME,
s.OSUSER,
s.MACHINE,
s.TERMINAL,
s.PROGRAM,
q.PARSING_SCHEMA_NAME,
'UPDATE' STATUS,
sys_context('userenv', 'ip_address') CLIENT_IP
from sys.v_$sql q, sys.v_$session s

where s.audsid =
(select userenv('SESSIONID') from dual)
and s.prev_sql_addr = q.address
AND s.PREV_HASH_VALUE = q.hash_value) a,
(select *
from t4
where id_product_order = :new.id_product_order
and id_column = :new.id_column) b
where a.PARSING_SCHEMA_NAME = 'GSINFO');
commit;
end if;
end;

---利用觸發器避免資料重複插入

create or replace trigger trig_duplication_col
before insert on t_product_order_col
for each row
declare
v_count number(5);
PRAGMA AUTONOMOUS_TRANSACTION;
begin
select count(*)
into v_count
from t_product_order_col
where id_product_order = :new.id_product_order
and id_column = :new.id_column;
if (v_count > 0) then
raise_application_error(-20000, 'data is duplication');
end if;
end;

-----測試

--建立檢視
create or replace view v_product_order_test as
select
distinct tc.col_desc
,a.user_name --使用者名稱稱
,cu.full_name
,a.ID id_user --使用者ID
,b.U_ID --CA證書編號U_ID
,b.ukey_no --Ukey物理編號
,f.id_product_order --使用產品例項ID
,por.CODE_PRODUCT_TPL --產品模版編號
,ptl.PRODUCT_TPL_NAME --產品名稱
,c.limit_cnt
,c.order_cnt
,tc.id
from t_user a --使用者 901
full outer join t_ukey b --Ukey 901
on a.id = b.id_user_use --901
left join T_PRODUCT_USER f --產品例項使用者關係
on a.id=f.id_user --931
left join T_PRODUCT_ORDER por
on f.ID_PRODUCT_ORDER=por.id
left join T_PRODUCT_TPL ptl
on por.CODE_PRODUCT_TPL=ptl.code
left join T_ORDER_LIMIT c
on c.id_user=a.id
and c.id_product_order=f.id_product_order
left join t_customer cu
on cu.id=a.id_customer
left join t_product_order_col pocl
on pocl.id_product_order=por.id
left join t_column tc
on tc.id=pocl.id_column;

--透過檢視查詢
select USER_NAME,
full_name
,ID_USER
,UKEY_NO
,ID_PRODUCT_ORDER
,CODE_PRODUCT_TPL
,PRODUCT_TPL_NAME,
col_desc,
id,
'GSINFO'
from v_product_order_test
where id_product_order='16231' and id in (1,2,3) ;

delete from (select * from t_product_order_col c where c.id_product_order='16230' and c.id_column in (4.5,6));
delete from (select * from t_product_order_col c where c.id_product_order='16231' and c.id_column in (1,2,3));

---查詢跟蹤表

select * from trigger_t5 where status='DELETE'and user_name='鮑總'
truncate table trigger_t1
truncate table trigger_t3
truncate table trigger_t4
truncate table trigger_t5

insert into t_product_order_col
select 16231, id, sysdate, 1, sysdate, null, null from t_column c where
id in (1,2,3);

select * from trigger_t1;
select * from trigger_t3;
select * from trigger_t4;
select * from trigger_t5;

比較完美的觸發器：

--建立臨時快取表
CREATE TABLE "GSINFO"."T1"
( "ID_PRODUCT_ORDER" VARCHAR2(50),
"ID_COLUMN" VARCHAR2(50),
"GRANT_TIME" DATE,
"GRANT_TYP" VARCHAR2(100),
"REVOKE_TIME" DATE,
"ID_USER_MODIFY" VARCHAR2(50),
"MODIFY_TIME" DATE
);

--建立trigger跟蹤表

CREATE TABLE "GSINFO"."trigger_log"
( "TIME1" DATE,
"SID" NUMBER,
"SERIAL#" NUMBER,
"USERNAME" VARCHAR2(30),
"OSUSER" VARCHAR2(30),
"MACHINE" VARCHAR2(64),
"TERMINAL" VARCHAR2(30),
"PROGRAM" VARCHAR2(48),
"PARSING_SCHEMA_NAME" VARCHAR2(50),
"STATUS" VARCHAR2(30),
"CLIENT_IP" VARCHAR2(30),
"ID_PRODUCT_ORDER" VARCHAR2(30),
"ID_COLUMN" VARCHAR2(30),
"COL_DESC" VARCHAR2(50),
"ID_USER" VARCHAR2(30),
"USER_NAME" VARCHAR2(30),
"FULL_NAME" VARCHAR2(30),
"UKEY_NO" VARCHAR2(50),
"OLD_COLUMN" VARCHAR2(40)
);

---建立insert 儲存過程

CREATE OR REPLACE PROCEDURE trigger_insert(s1 in varchar2, s2 in varchar2) as
begin
insert into trigger_log
select time1,
sid,
SERIAL#,
USERNAME,
OSUSER,
MACHINE,
TERMINAL,
PROGRAM,
PARSING_SCHEMA_NAME,
STATUS,
CLIENT_IP,
id_product_order,
id_column,
col_desc,
id_user,
user_name,
full_name,
ukey_no,
''
from (select sysdate time1,
s.SID,
s.SERIAL#,
s.USERNAME,
s.OSUSER,
s.MACHINE,
s.TERMINAL,
s.PROGRAM,
q.PARSING_SCHEMA_NAME,
'INSERT' STATUS,
sys_context('userenv', 'ip_address') CLIENT_IP
from sys.v_$sql q, sys.v_$session s
where s.audsid = (select userenv('SESSIONID') from dual)
and s.prev_sql_addr = q.address
AND s.PREV_HASH_VALUE = q.hash_value) ab,
(select a.id_product_order,
id_column,
col_desc,
id_user,
d.user_name,
full_name,
(select user from dual) currname,
ukey_no,
''
from t1 a,
t_product_user b,
t_column c,
t_user d,
t_customer cu,
t_ukey uk
where a.id_product_order = b.id_product_order
and a.id_column = c.id
and b.id_user = d.id
and cu.id = d.id_customer
and b.id_user = uk.id_user_use) cd
where ab.PARSING_SCHEMA_NAME = cd.currname
and id_product_order = s1
and id_column = s2
group by time1,
sid,
SERIAL#,
USERNAME,
OSUSER,
MACHINE,
TERMINAL,
PROGRAM,
PARSING_SCHEMA_NAME,
STATUS,
CLIENT_IP,
id_product_order,
id_column,
col_desc,
id_user,
user_name,
full_name,
ukey_no;
commit;
end;

---建立delete儲存過程

CREATE OR REPLACE PROCEDURE trigger_delete(s1 in varchar2, s2 in varchar2) as
begin
insert into trigger_log
select time1,
sid,
SERIAL#,
USERNAME,
OSUSER,
MACHINE,
TERMINAL,
PROGRAM,
PARSING_SCHEMA_NAME,
STATUS,
CLIENT_IP,
id_product_order,
id_column,
col_desc,
id_user,
user_name,
full_name,
ukey_no,
''
from (select sysdate time1,
s.SID,
s.SERIAL#,
s.USERNAME,
s.OSUSER,
s.MACHINE,
s.TERMINAL,
s.PROGRAM,
q.PARSING_SCHEMA_NAME,
'DELETE' STATUS,
sys_context('userenv', 'ip_address') CLIENT_IP
from sys.v_$sql q, sys.v_$session s
where s.audsid = (select userenv('SESSIONID') from dual)
and s.prev_sql_addr = q.address
AND s.PREV_HASH_VALUE = q.hash_value) ab,
(select a.id_product_order,
id_column,
col_desc,
id_user,
d.user_name,
full_name,
(select user from dual) currname,
ukey_no
from t1 a,
t_product_user b,
t_column c,
t_user d,
t_customer cu,
t_ukey uk
where a.id_product_order = b.id_product_order
and a.id_column = c.id
and b.id_user = d.id
and cu.id = d.id_customer
and b.id_user = uk.id_user_use) cd
where ab.PARSING_SCHEMA_NAME = cd.currname
and id_product_order = s1
and id_column = s2
group by time1,
sid,
SERIAL#,
USERNAME,
OSUSER,
MACHINE,
TERMINAL,
PROGRAM,
PARSING_SCHEMA_NAME,
STATUS,
CLIENT_IP,
id_product_order,
id_column,
col_desc,
id_user,
user_name,
full_name,
ukey_no;
commit;
end;

----建立觸發器

create or replace trigger t_trigger
after INSERT OR UPDATE or DELETE OF id_product_order, id_column on gsinfo.t_product_order_col
For each row
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;

Begin
IF INSERTing THEN
insert into t1
select :new.id_product_order, :new.id_column, sysdate, '', '', '', ''
from t_product_order_col
where rownum = 1;
commit;
trigger_insert(:new.id_product_order, :new.id_column);

ELSIF deleting THEN
insert into t1
select :old.id_product_order, :old.id_column, sysdate, '', '', '', ''
from t_product_order_col
where rownum = 1;
commit;
trigger_delete(:old.id_product_order, :old.id_column);

ELSIF updating THEN

insert into trigger_log
select time1,
sid,
SERIAL#,
USERNAME,
OSUSER,
MACHINE,
TERMINAL,
PROGRAM,
PARSING_SCHEMA_NAME,
STATUS,
CLIENT_IP,
id_product_order,
id_column,
col_desc,
id_user,
user_name,
full_name,
ukey_no,
:old.id_column
from (select sysdate time1,
s.SID,
s.SERIAL#,
s.USERNAME,
s.OSUSER,
s.MACHINE,
s.TERMINAL,
s.PROGRAM,
q.PARSING_SCHEMA_NAME,
'UPDATE' STATUS,
sys_context('userenv', 'ip_address') CLIENT_IP
from sys.v_$sql q, sys.v_$session s
where s.audsid = (select userenv('SESSIONID') from dual)
and s.prev_sql_addr = q.address
AND s.PREV_HASH_VALUE = q.hash_value) ab,
(select a.id_product_order,
id_column,
col_desc,
id_user,
d.user_name,
full_name,
(select user from dual) currname,
ukey_no
from t1 a,
t_product_user b,
t_column c,
t_user d,
t_customer cu,
t_ukey uk
where a.id_product_order = b.id_product_order
and a.id_column = c.id
and b.id_user = d.id
and cu.id = d.id_customer
and b.id_user = uk.id_user_use) cd
where ab.PARSING_SCHEMA_NAME = cd.currname
and id_product_order = :new.id_product_order
and id_column = :new.id_column
group by time1,
sid,
SERIAL#,
USERNAME,
OSUSER,
MACHINE,
TERMINAL,
PROGRAM,
PARSING_SCHEMA_NAME,
STATUS,
CLIENT_IP,
id_product_order,
id_column,
col_desc,
id_user,
user_name,
full_name,
ukey_no;
commit;
end if;

end;

-----利用觸發器避免資料重複插入

create or replace trigger trig_duplication_col
before insert on t_product_order_col
for each row
declare
v_count number(5);
PRAGMA AUTONOMOUS_TRANSACTION;
begin
select count(*)
into v_count
from t_product_order_col
where id_product_order = :new.id_product_order
and id_column = :new.id_column;
if (v_count > 0) then
raise_application_error(-20000, 'data is duplication');
end if;
end;

------測試
--檢視123是否存在
select USER_NAME,
full_name
,ID_USER
,UKEY_NO
,ID_PRODUCT_ORDER
,CODE_PRODUCT_TPL
,PRODUCT_TPL_NAME,
col_desc,
'GSINFO'
from gsinfo.v_product_order_test
where id_product_order='16231' and id in(1,2,3)

---如果不存在插入
insert into gsinfo.t_product_order_col
select 16231, id, sysdate, 1, sysdate, null, null
from gsinfo.t_column c
where id in (1, 2, 3);

---跟蹤trigger_log表
select * from trigger_log;

---刪除

delete from (select * from gsinfo.t_product_order_col c where c.id_product_order='16231' and c.id_column in (1,2,3));

---跟蹤trigger_log表
select * from trigger_log;

--update
update t_product_order_col set id_column=1 where id_product_order='16231' and id_column =2;

---跟蹤trigger_log表
select * from trigger_log;

根據業務寫觸發器（oracle觸發器片）

相關文章