最近公司需要根据业务写一个触发器,要求当某个用户添加删除更新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;
相当烂的触发器:
--创建表
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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30345407/viewspace-2105322/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30345407/viewspace-2105322/