create or replace trigger tri_xxx_delete
after delete on xxxx
begin
insert into xxx_bak select * from xxx where xxx.id = 
ld.id;
end;
更改insert into xxx_bak select * from xxx where xxx.id = : old.id;
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Declare
L_action employees_log.action%type;
Begin
if inserting then
l_action:=’Insert’;
elsif updating then
l_action:=’Update’;
elsif deleting then
l_action:=’Delete’;
else
raise_application_error(-20001,’You should never ever get this error.’);
Insert into employees_log(
Who,action,when)
Values( user, l_action,sysdate);
End;
/
不过有个问题，不是很方便：
SQL> create table test
2 (id number ,
3 user_name varchar2(16)
4 );
Table created.
SQL> create table test_bak as select * from test;
Table created.
SQL> create or replace trigger catch_sql
BEFORE DELETE ON test
FOR EACH ROW
begin
insert into test_bak select * from test where id=:old.id;
end;
/ 2 3 4 5 6 7
Trigger created.
SQL> insert into test values(1,'liubin');
1 row created.
SQL> insert into test values(2,'test');
1 row created.
SQL> select * from test;
ID USER_NAME
---------- ----------------
1 liubin
2 test
SQL> select * from test_bak;
no rows selected
SQL> select * from test_bak;
no rows selected
SQL> delete from test;
delete from test
*
ERROR at line 1:
ORA-04091: table APP.TEST is mutating, trigger/function may not see it
ORA-06512: at "APP.CATCH_SQL", line 2
ORA-04088: error during execution of trigger 'APP.CATCH_SQL'
SQL> delete from test where id=1;
delete from test where id=1
*
ERROR at line 1:
ORA-04091: table APP.TEST is mutating, trigger/function may not see it
ORA-06512: at "APP.CATCH_SQL", line 2
ORA-04088: error during execution of trigger 'APP.CATCH_SQL'
SQL> create or replace trigger catch_sql
BEFORE DELETE ON test
FOR EACH ROW
declare
v_id number;
v_user_name varchar2(16);
begin
v_id:=:old.id;
v_user_name:=:old.user_name;
insert into test_bak values(v_id,v_user_name);
end;
/ 2 3 4 5 6 7 8 9 10 11 12
Trigger created.
SQL> delete from test;
2 rows deleted.
SQL> select * from test_bak;
ID USER_NAME
---------- ----------------
1 liubin
2 test
SQL> select * from test;
no rows selected
SQL>
也就是说，在触发器里面不能用select那个记录了，我只好用笨方法把每个值取出来再插进去。
http://download-west.oracle.com/doc...dg13trg.htm#786
不明白，为什么还要加个中间变量，直接使用ld. 的值不就可以了吗？
在触发器中不能访问被触发表，只能用:new和ld访问当前记录的值。
Trackback: http://tb.donews.net/TrackBack.aspx?PostId=202878