sql 代码 --[6]// oracle trigger -- 实例 1------------------------ -- 创建触发器，当用户对 test 表执行 dml 语句时，将相关信息记录到日志表 -- 创建测试表 create table test ( t_id number(4), t_name varchar2(20), t_age number(2), t_sex char );
sql 代码
--[6]// oracle trigger
--实例1------------------------
--创建触发器，当用户对test表执行dml语句时，将相关信息记录到日志表
--创建测试表
create table test
(
t_id number(4),
t_name varchar2(20),
t_age number(2),
t_sex char
);
--创建记录测试表
create table test_log
(
l_user varchar2(15),
l_type varchar2(15),
l_date varchar2(30)
);
--创建触发器
create or replace trigger test_trigger
after delete or insert or update on test
declare
v_type test_log.l_type%type;
begin
if inserting then --insert触发
v_type := 'insert';
dbms_output.put_line('记录已经成功插入，并已记录到日志');
elsif updating then --update触发
v_type := 'update';
dbms_output.put_line('记录已经成功更新，并已记录到日志');
elsif deleting then
v_type := 'delete';
dbms_output.put_line('记录已经成功删除，并已记录到日志');
end if;
insert into test_log values(user,v_type,
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end;
/
--下面我们来分别执行dml语句
insert into test values(101,'zhao',22,'m');
update test set t_age = 30 where t_id = 101;
delete test where t_id = 101;
--然后查看效果
select * from test;
select * from test_log;
--实例2------------------------
--创建触发器，它将映射emp表中每个部门的总人数和总工资
--创建映射表
create table dept_sal
as
select deptno,count(empno) as total_emp,sum(sal) as total_sal from emp group by deptno;
desc dept_sal;
--创建触发器
create or replace trigger emp_info
after insert or update or delete on emp
declare
cursor cur_emp is
select deptno,count(empno) as total_emp,sum(sal) as total_sal from emp group by deptno;
begin
delete dept_sal; --触发时首先删除映射表信息
for v_emp in cur_emp loop
--dbms_output.put_line(v_emp.deptno || v_emp.total_emp || v_emp.total_sal);
--插入数据
insert into dept_sal
values(v_emp.deptno,v_emp.total_emp,v_emp.total_sal);
end loop;
end;
/
--对emp表进行dml操作
insert into emp(empno,deptno,sal) values('123','10',10000);
select * from dept_sal;
delete emp where empno=123;
select * from dept_sal;
--实例3------------------------
--创建触发器，它记录表的删除数据
--创建表
create table employee
(
id varchar2(4) not null,
name varchar2(15) not null,
age number(2) not null,
sex char not null
);
desc employee;
--插入数据
insert into employee values('e101','zhao',23,'m');
insert into employee values('e102','jian',21,'f');
--创建记录表
create table old_employee as
select * from employee;
desc old_employee;
--创建触发器
create or replace trigger tig_old_emp
after delete on employee --
for each row --语句级触发，即每一行触发一次
begin
insert into old_employee
values(:old.id,:old.name,:old.age,:old.sex); --:old代表旧值
end;
/
--下面进行测试
delete employee;
select * from old_employee;
--实例4------------------------
--创建触发器，利用视图插入数据
--创建表
create table tab1 (tid number(4) primary key,tname varchar2(20),tage number(2));
create table tab2 (tid number(4),ttel varchar2(15),tadr varchar2(30));
--插入数据
insert into tab1 values(101,'zhao',22);
insert into tab1 values(102,'yang',20);
insert into tab2 values(101,'13761512841','anhuisuzhou');
insert into tab2 values(102,'13563258514','anhuisuzhou');
--创建视图连接两张表
create view tab_view as
select tab1.tid,tname,ttel,tadr from tab1,tab2
where tab1.tid = tab2.tid;
--创建触发器
create or replace trigger tab_trigger
instead of insert on tab_view
begin
insert into tab1(tid,tname) values(:new.tid,:new.tname);
insert into tab2(ttel,tadr) values(:new.ttel,:new.tadr);
end;
/
--现在就可以利用视图插入数据
insert into tab_view values(105,'zhaoyang','13886681288','beijing');
--查看效果
select * from tab_view;
--实例5------------------------
--创建触发器，比较emp表中更新的工资
create or replace trigger sal_emp
before update on emp
for each row
begin
if :old.sal > :new.sal then
dbms_output.put_line('工资减少');
elsif :old.sal dbms_output.put_line('工资增加');
else
dbms_output.put_line('工资未作任何变动');
end if;
dbms_output.put_line('更新前工资 ：' || :old.sal);
dbms_output.put_line('更新后工资 ：' || :new.sal);
end;
/
--执行update查看效果
update emp set sal = 3000 where empno = '7788';
--实例6------------------------
--创建触发器，将操作create、drop存储在log_info表
--创建表
create table log_info
(
manager_user varchar2(15),
manager_date varchar2(15),
manager_type varchar2(15),
obj_name varchar2(15),
obj_type varchar2(15)
);
--创建触发器
create or replace trigger trig_log_info
after create or drop on schema
begin
insert into log_info
values(user,sysdate,sys.dictionary_obj_name,sys.dictionary_obj_owner,
sys.dictionary_obj_type);
end;
/
--测试语句
create table a(id number);
create type aa as object(id number);
/
drop table a;
drop type aa;
--查看效果
select * from log_info;
--相关数据字典-----------------------------------------------------//
select * from user_triggers;
select * from all_triggers;
select * from dba_triggers; --必须以dba身份登陆才能使用此数据字典
--启用和禁用
alter trigger trigger_name disable;
alter trigger trigger_name enable;
------------------------------------------------------------------------------------------end//
关于在　oracle trigger（insert）事件中调用存储过程的问题
最近用oracle触发器写了点东西,不过,确发现 ora-04092、ora-06512　等错误。怎么办？
最初，我也被弄得手忙脚乱。
于是，我查看　ora-04092　提示的错误信息，是ｒｏｌｌｂａｃｋ和ｃｏｍｍｉｔ　命令不能出现在触发器中。
于是，开始解决问题。发现，当在触发器里加入事务性操作时，会有如下错误：ora-04092 cannot string in a trigger. 解决办法，在触发器里加入声明pragma autonomous_transaction，好了，不在弹出错误信息。
可是，我通过触发器调用存储过程，却发现存储过程执行后，数据没任何变化（我的存储过程是执行一次，删除一次中间表数据。）。难道是我的存储过程出了问题？单步调试，没任何问题。执行结果正确。
通过多次测试，发现原来是触发触发器的时候（触发事件为　insert　after）由于没有提交（ｃｏｍｍｉｔ）数据。在中间表中就查不到任何数据。处理方法，
第一步、新建一个中间表，做为触发器触发事件发生的中间表。
第二步、在中间表插入数据，提交。
第三步、在第一步新建的中间表中插入数据，提交执行成功。
第四步、检查结果，正常。
能否在表的触发器中当一记录变化修改另一记录
sql> create or replace trigger addnum
  2  before update on test1.test1
  3  for each row
  4  begin
  5  update test1 set group1=(:new.group1+1) where group1=2;
  6  end;
  7  /
触发器已创建
已用时间:  00: 00: 00.00
sql> update test1 set group1=7 where group1=1;
update test1 set group1=7 where group1=1
       *
error 位于第 1 行:
ora-04091: 表 test1.test1 发生了变化，触发器/函数不能读
ora-06512: 在test1.addnum, line 2
ora-04088: 触发器 'test1.addnum' 执行过程中出错
这样是肯定不行的，因为触发器会引起死循环。用表层触发器，但表层是不能用:new或:old
sql> create or replace procedure test3(i number,m number)
  2  as
  3  a number;
  4  str char(1000);
  5  begin
  6  update test1 set group1=i where group1=m;
  7  execute immediate 'commit';
  8  a:=i;
  9  for a in i..m loop
 10  update test1 set group1=i+1 where group1=i;
 11  execute immediate 'commit';
 12  end loop;
 13  end test3;
 14  /
过程已创建。
已用时间:  00: 00: 00.00
sql> exec test3(2,4);
begin test3(2,4); end;
*
error 位于第 1 行:
ora-04091: 表 test1.test1 发生了变化，触发器/函数不能读
ora-06512: 在test1.addnum, line 2
ora-04088: 触发器 'test1.addnum' 执行过程中出错
ora-06512: 在test1.test3, line 6
ora-06512: 在line 1
1、使用视图或者临时表
you can do:
  1. rename your table with another name;
  2. create a view for select * from the table with the original name of the table;
  3. create a instead of trigger on the view, in the view you can access and update yur base
table.
example of instead of trigger for this case:
[ code start ]
create table t1(
  id        number(6)    primary key
 ,pid       number(6)
 ,value     number(15,2)
 ,f1        varchar2(10)
 ,f2        varchar2(20)
);
create or replace view t1_v as select * from t1;
create or replace trigger bug_t1_v
instead of update on t1_v
for each row
declare
  procedure update_parents(i_id in number, i_value in number);
  procedure update_parents(i_id in number, i_value in number) is
  begin
    declare
      l_pid t1.pid%type;
    begin
      select pid into l_pid
        from t1
       where id = i_id;
      if l_pid 0 then 
        update t1 set value = nvl(value,0) + nvl(i_value,0)
         where id = l_pid;
        update_parents(l_pid, i_value);
      end if;
    exception
      when no_data_found then
        null; 
    end;       
  end update_parents;
begin
  --
  -- update value field for current record and parent records 
  --
  if nvl(:new.value,0) - nvl(:old.value,0) 0 then
     update t1 set value = value + nvl(:new.value,0) - nvl(:old.value,0)
      where id = :new.id;
     update_parents(:new.id, nvl(:new.value,0) - nvl(:old.value,0)); 
  end if;
  --
  -- update others fields
  --
  update t1 set f1 = :new.f1
               ,f2 = :new.f2
   where id = :new.id;
end;
--
-- testing
--
-- with this view: t1_v
--
begin
  for i in 1..50 loop
    insert into t1_v values(i, i-1, 0, '', '');
  end loop;
end;
/
delete from t1_v;
begin
  for i in 1..50 loop
    insert into t1_v values(i, i-1, 0, '', '');
  end loop;
end;
/
update t1_v set f1 = 'test' where id = 49;
update t1_v set value = value + 5 , f1 = 'aa', f2 = 'bb'
where id = 50;
[ code end ]
使用临时表
11:04:36 sql> create global temporary table tmp(
11:04:39   2  rid varchar2(20))on commit delete rows;
表已创建。
已用时间:  00: 00: 00.16
11:07:30 sql> create or replace trigger trg_tb
11:07:36   2  after insert on tb
11:07:36   3  for each row
11:07:36   4  begin
11:07:36   5  if :new.col2='laji' then
11:07:36   6   insert into tmp values(:new.rowid);
11:07:36   7  end if;
11:07:36   8  end trg_tb;
11:07:36   9  /
触发器已创建
已用时间:  00: 00: 00.47
11:07:36 sql> create or replace trigger trg_tb_del
11:07:44   2  after insert on tb
11:07:46   3  begin
11:07:46   4  delete from tb where exists(select 1 from tmp where rid=tb.rowid);
11:07:46   5  end trg_tb_del;
11:07:46   6  /
触发器已创建
已用时间:  00: 00: 00.31
11:08:59 sql> select count(1) from tb;
count(1)
----------
        22
已用时间:  00: 00: 00.00
11:09:08 sql> insert into tb(col1,col2) values('aaa','laji');
已创建 1 行。
已用时间:  00: 00: 00.00
11:09:12 sql> select count(1) from tb;
count(1)
----------
        22
已用时间:  00: 00: 00.16
11:09:14 sql>
from metalink
problem description -------------------
oracle does not allow you to read a mutating table in a row trigger because if you can read it,
the information may be incorrect (not read consistent). if you attempt this, the following error
is returned:
ora-04091
table %s.%s is mutating, trigger/function may not see it
however, you can perform this operation in a statement trigger.
one way to handle this situation is to use a package pl/sql table to store rowids of updated
records in a row trigger, and reprocess the updated
 records in a statement trigger.
below is an example.
important note --------------
note that there are concurrency issues with this if more than one session tries to perform
operations simultaneously.
this is not intended as a total solution, but as the framework to help show one option.
 example workaround ------------------
[code:1:50147eb56b]
create or replace package emp_pkg as
type
emp_tab_type is table of rowid index by binary_integer;
emp_tab
 emp_tab_type;
emp_index binary_integer;
 end emp_pkg;
 /
create or replace trigger emp_bef_stm_all
 before insert or update or delete on emp
 begin
/*
remember to reset the pl/sql table before each statement
*/
emp_pkg.emp_index := 0;
 end;
 /
create or replace trigger emp_aft_row_all
 after insert or update or delete on emp
 for each row
 begin
/*
store the rowid of updated record into global pl/sql table
*/
emp_pkg.emp_index := emp_pkg.emp_index + 1;
emp_pkg.emp_tab(emp_pkg.emp_index) := :new.rowid;
 end;
 /
create or replace trigger emp_aft_stm_all
 after
insert or update or delete on emp
 begin
for i in 1 .. emp_pkg.emp_index loop
 /*
re-process the updated records.
 there is no restriction here.
 */
 dbms_output.put_line(emp_pkg.emp_tab(i));
end loop;
emp_pkg.emp_index := 0;
 end;
 /
怎么样把提交的数据通过触发器进行验证，如果不合条件，直接删除？
楼主andywzw（）2004-09-30 11:04:40 在 oracle / 开发提问
在应用程序中，有些数据是垃圾数据，现在程序在前段提交的时候没有验证机制，补做代价比较大，我现在想能不能在后台oracle通过触发器，对插入动作进行验证，如果发现垃圾数据，直接删除，这样比做存储过程进行定时监控要减少系统资源的占用，但是我不知道如何实现，请高手指教！问题点数：100、回复次数：32top
1 楼drabit（square）回复于 2004-09-30 11:14:23 得分 0
这样做违反事务的一致性要求，oracle不会提供这种手段的。一次插入的数据要么都插入，要么都不插入。top
2 楼bluelamb（bluelamb）回复于 2004-09-30 11:18:48 得分 0
只能够在提交前验证top
3 楼bzszp（songzip）回复于 2004-09-30 11:19:44 得分100
使用临时表
11:04:36   sql>   create   global   temporary   table   tmp(  
  11:04:39       2     rid   varchar2(20))on   commit   delete   rows;
表已创建。
已用时间:     00:   00:   00.16  
  11:07:30   sql>   create   or   replace   trigger   trg_tb    
  11:07:36       2     after   insert   on   tb  
  11:07:36       3     for   each   row  
  11:07:36       4     begin  
  11:07:36       5     if   :new.col2='laji'   then  
  11:07:36       6       insert   into   tmp   values(:new.rowid);  
  11:07:36       7     end   if;  
  11:07:36       8     end   trg_tb;  
  11:07:36       9     /
触发器已创建
已用时间:     00:   00:   00.47  
  11:07:36   sql>   create   or   replace   trigger   trg_tb_del  
  11:07:44       2     after   insert   on   tb  
  11:07:46       3     begin  
  11:07:46       4     delete   from   tb   where   exists(select   1   from   tmp   where   rid=tb.rowid);  
  11:07:46       5     end   trg_tb_del;  
  11:07:46       6     /
触发器已创建
已用时间:     00:   00:   00.31  
  11:08:59   sql>   select   count(1)   from   tb;
count(1)  
  ----------  
                  22
已用时间:     00:   00:   00.00  
  11:09:08   sql>   insert   into   tb(col1,col2)   values('aaa','laji');
已创建   1   行。
已用时间:     00:   00:   00.00  
  11:09:12   sql>   select   count(1)   from   tb;
count(1)  
  ----------  
                  22
已用时间:     00:   00:   00.16  
  11:09:14   sql>
说明刚才插入的数据已经被删除掉了。top
4 楼andywzw（）回复于 2004-09-30 11:20:15 得分 0
实际上我的想法是这样的，先让它插入目标表，同时触发检查这个刚才插入的语句是否符合条件，如果不符合就从目标表中直接删除，如果符合，就不做任何操作。top
5 楼bzszp（songzip）回复于 2004-09-30 11:21:37 得分 0
通过一个行级触发器+一个表级触发器+临时表解决。top
6 楼andywzw（）回复于 2004-09-30 11:25:09 得分 0
那我不用再改写前台的应用程序了吗？前台的插入是通过java实现的，临时表是否影响正是数据的插入？top
7 楼andywzw（）回复于 2004-09-30 11:28:35 得分 0
bzszp(songzip)   的方案都是很专业的，我太佩服了！我做个实验看结果！top
8 楼bzszp（songzip）回复于 2004-09-30 11:31:19 得分 0
不会的，用on     commit     delete     rows;   方式创建临时表，在会话  
  commit以后oracle自动清空数据。
top
9 楼andywzw（）回复于 2004-09-30 11:38:26 得分 0
对了，你的临时表只有一个字段啊，能满足我对多个字段的验证吗？top
10 楼bzszp（songzip）回复于 2004-09-30 11:41:33 得分 0
没有问题，这个是存放的rowid，对于每一行数据都是唯一的。  
  判断是在行级触发器内部判断，如果是垃圾数据  
  纪录:new.rowid到临时表  
  在表级触发器里面集中进行删除。top
11 楼andywzw（）回复于 2004-09-30 11:44:18 得分 0
我觉得这样的方式比我们昨天讨论的解决问题的方案更好吧。这样可以避免不断的执行存储过程导致系统性能有影响。top
12 楼drabit（square）回复于 2004-09-30 12:10:08 得分 0
study！top
13 楼fightintokyo（）回复于 2004-09-30 13:11:24 得分 0
bzszp(songzip)，good！
请教一个初级的问题。上面的解决方案中，行级触发器会确保在表级触发器前被触发么？（对触发器不太了解）top
14 楼zhaokeke2004（男人·海洋）回复于 2004-09-30 13:27:06 得分 0
学习top
15 楼bzszp（songzip）回复于 2004-09-30 13:34:32 得分 0
to：fightintokyo(黑龙)    
  是的
性能方面应该会好一些吧。  
  毕竟直接通过rowid进行删除，而且是在commit之前就删除了  
  这样那些不需要的数据就没有写回数据文件。top
16 楼andywzw（）回复于 2004-09-30 13:34:53 得分 0
bzszp(songzip)，请把你的tb表，desc   tb   给我看，我怎么做不下去了！top
17 楼andywzw（）回复于 2004-09-30 13:39:25 得分 0
bzszp(songzip)，是个专家级的人物，解决问题准确，耐心，细致，大家向他致敬！！！top
18 楼fightintokyo（）回复于 2004-09-30 13:56:51 得分 0
谢谢bzszp(songzip)。
查了一下资料，果然触发器是按照以下顺序执行的。  
  1.执行before表级触发器。  
  2.以受影响的行进行循环。  
      a.执行before行级触发器。（commit为止该行被lock）  
      b.执行after行级触发器。  
  3.目标表定义的整合性check  
  4.执行after表级触发器。
再请教一个问题。  
  为什么不在行级触发器中直接把不符合条件的数据delete了呢？  
  这样就不需要临时表和行级触发器了亚。top
19 楼andywzw（）回复于 2004-09-30 14:00:30 得分 0
bzszp(songzip)，请把你的tb表，desc   tb   给我看，我怎么做不下去了！top
20 楼bzszp（songzip）回复于 2004-09-30 14:01:30 得分 0
这样会有问题的，行级触发器对于正在处理的表数据有操作限制。top
21 楼bzszp（songzip）回复于 2004-09-30 14:01:52 得分 0
13:50:23   sql>   desc   tb;  
    名称                                                                             空?             类型  
    -----------------------------------------   --------   --------------  
    col1                                                                                               varchar2(10)  
    col2                                                                                               varchar2(30)  
    colnew                                                                                           varchar2(20)
13:52:32   sql>top
22 楼andywzw（）回复于 2004-09-30 14:17:20 得分 0
bzszp(songzip)     这办法绝对很棒，但是问题是  
    create   or   replace   trigger   trg_tb    
  after   insert   on   tb   for   each   row  
  begin  
  if   :new.col2='laji'   then  
    insert   into   tmp   values(:new.rowid);  
  end   if;  
  end   trg_tb;  
  /  
  这个里面的你现在指定了col2,我要指定多列是不是用    
  if   :new.col2='laji'   or   :new.col1='xxx'   or     :new.coln='nnn'     then  
  另外，这里的=后面的值是唯一的有局限性，我怎么从一个表，例如bad_word里面获得这些值？top
23 楼andywzw（）回复于 2004-09-30 14:23:43 得分 0
sql>   desc   bad_word  
    名称                                                                             空?             类型  
    -----------------------------------------   --------   -------------  
    id                                                                                                   number(10)  
    key_word                                                                                       varchar2(60)top
24 楼bzszp（songzip）回复于 2004-09-30 14:27:22 得分 0
用select   判断,另外声明一个v_num   number的变量。  
  select   count(*)   into   v_num   from   bad_word    
  where   bad_word.colname=:new.col1;  
  if   v_num>0   then   --属于包含垃圾信息的行  
      ...  
  end   if;top
25 楼bzszp（songzip）回复于 2004-09-30 14:28:25 得分 0
select   count(*)   into   v_num   from   bad_word    
  where   key_word=:new.col1;top
26 楼andywzw（）回复于 2004-09-30 14:58:01 得分 0
不好意思，我不知道怎么申明v_num   这个变量   ：）top
27 楼bzszp（songzip）回复于 2004-09-30 15:02:09 得分 0
如：  
  create     or     replace     trigger     trg_tb          
  after     insert     on     tb     for     each     row  
  declare                     --声明变量开始    
  v_num   number;     --声明number类型的变量v_num  
  begin        
  ...  
  top
28 楼andywzw（）回复于 2004-09-30 15:23:44 得分 0
bzszp(songzip)   我试验成功了！再问一下您，如果这种办法用于留言版后台过滤程序是不是很有价值的？比在前台更加主动，减少维护量。top
29 楼bzszp（songzip）回复于 2004-09-30 15:28:50 得分 0
减少了前台的工作量  
  但是增加了后台的工作量  
  这个根据实际情况来定吧。top
30 楼andywzw（）回复于 2004-09-30 16:32:34 得分 0
谢谢，结分，祝bzszp(songzip)   及其各位国庆节愉快！top
31 楼andywzw（）回复于 2004-09-30 18:13:13 得分 0
create   or   replace   trigger   trg_leave_word  
  after   insert   on   leave_word   for   each   row  
  declare    
  v_num   number;  
  begin  
  select   count(*)   into   v_num   from   bad_word    
  where   key_word   like   '%'||:new.title||'%'   or   key_word   like   '%'||:new.content||'%';  
  if   v_num>0   then      
    insert   into   tmp   values(:new.rowid);  
  end   if;  
  end   trg_leave_word;  
  /
这个触发器还是有问题的，过滤了content但不能过滤title   ，如何解决，难道我的   or条件有问题？top
32 楼andywzw（）回复于 2004-10-01 02:02:28 得分 0
我找到解决的办法了，改写成如下样子完全可以使用：  
  create   or   replace   trigger   trg_leave_word  
  after   insert   on   leave_word   for   each   row  
  declare    
  v_num   number;  
  begin  
    select   count(*)   into   v_num   from   bad_word    
      where   instr(:new.title,key_word)>0   or   instr(:new.content,key_word)>0;  
    if   v_num>0   then      
      insert   into   tmp   values(:new.rowid);  
    end   if;  
  end   trg_leave_word;