我的环境是oracle 10g,用户scott,做一个触发器试试。create table emp1
as
select * from emp;--建立触发器
create or replace trigger t2
after update on emp1
for each row -- local variables here
begin
update emp1 e
set e.hiredate=sysdate
where e.empno=:new.empno;
EXCEPTION
WHEN OTHERS THEN
NULL;
end;
--update数据,验证触发器
update emp1 e
set e.ename='smith1'
where e.empno=7369;
commit;
select * from emp1 where e.empno=7369;结果触发器没有起作用,时间没有修改成当前时间,还是原来的,这是怎么回事呢?
1 7369 smith1 CLERK 7902 1980-12-17 800.00 20
--建立触发器
create or replace trigger t2
after update on emp1
for each row
declare
pragma autonomous_transaction;
-- local variables here
begin
update emp1 e
set e.hiredate=sysdate
where e.empno=:new.empno;
EXCEPTION
WHEN OTHERS THEN
NULL;
end;
update emp1 e
set e.hiredate=sysdate
where e.empno=:new.empno;
EXCEPTION
WHEN OTHERS THEN
NULL;取没有做什么事,你可以在这里加上一个dbms_output.put_line打印一下,就知道了。解决方法,使用自治事务。用法详见
http://www.inthirties.com/thread-84-1-1.html
update emp1 e
set e.hiredate=sysdate
where e.empno=:new.empno;
EXCEPTION
WHEN OTHERS THEN
NULL;
end;可改为如下后,再试试(1.在emp1表中进行修改;2.在emp表中查看触发修改的结果。):
begin
update emp e
set e.hiredate=sysdate
where e.empno=:new.empno;
EXCEPTION
WHEN OTHERS THEN
NULL;
end;
empno应该是主键,那么楼主要修改的应该是当前修改的记录的hiredate值
after改成before
这样就行了:
create or replace trigger t2
before update on emp1
for each row -- local variables here
begin
:new.hiredate:=sysdate;
EXCEPTION
WHEN OTHERS THEN
NULL;
end;
但前面的办法,即使是用了自治事务,仍然是提示资源被锁定,无法正确更新hiredate
create or replace trigger t2
after update on emp1
for each row
declare
pragma autonomous_transaction;
begin
update emp1 e set e.hiredate=sysdate
where e.empno=:new.empno;
commit;
--exception
-- when others then null;
end;
执行提示:
update emp1 set sal=805 where empno=7369
*
第 1 行出现错误:
ORA-00060: 等待资源时检测到死锁
ORA-06512: 在 "SCOTT.T2", line 4
ORA-04088: 触发器 'SCOTT.T2' 执行过程中出错