oracle触发器更新同一张表

注意点:

1. 更新同一张表，必须是before，如果是after，会引起死锁.
2. 貌似需要加自治事务，PRAGMA AUTONOMOUS_TRANSACTION.
3. 不用update更新语句，用:new.字段名赋值即可.

create or replace trigger mdt_server_update_tri

before insert or update

on mdt_server_info_tracking_t

for each row

declare

PRAGMA AUTONOMOUS_TRANSACTION;

begin

– 更新时间、IP

:new.modify_date := sysdate;

:new.modify_ip := SYS_CONTEXT(‘USERENV’,‘IP_ADDRESS’);

commit;

– 详细记录表

insert into mdt_server_info_detail_t

select * from mdt_server_info_tracking_t where id = :old.id;

commit;

end mdt_server_update_tri;