现在有两张数据库表,一张是FILE_SHARE主键是FS_ID,另一张表是FILE_MARK,通过外键FM_SHARE_ID与FILE_SHARE表的FS_ID相关。现在需要写一个触发器在删除FILE_SHARE表中记录后自动删除FILE_MARK表中FM_SHARE_ID字段与FILE_SHARE表中被删记录的FS_ID字段相同的记录。
代码:
create or replace trigger TRGDELETE_FILESHARE_FILEMARK
after delete on FILE_SHARE
for each row
begin
delete from FILE_MARK where FM_SHARE_ID = :OLD.FS_ID;
end;编译正常,当FILE_SHARE表中的记录被删除后,FILE_MARK表的记录还在,请各位帮忙解决,谢谢!!
分不多请谅解
代码:
create or replace trigger TRGDELETE_FILESHARE_FILEMARK
after delete on FILE_SHARE
for each row
begin
delete from FILE_MARK where FM_SHARE_ID = :OLD.FS_ID;
end;编译正常,当FILE_SHARE表中的记录被删除后,FILE_MARK表的记录还在,请各位帮忙解决,谢谢!!
分不多请谅解
触发器不能加commit,除非指定自治事务,改成下面这样,就可以了:create or replace trigger TRGDELETE_FILESHARE_FILEMARK
after delete on FILE_SHARE
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
delete from FILE_MARK where FM_SHARE_ID = :OLD.FS_ID;
commit;
end;
触发器不能加commit,除非指定自治事务,改成下面这样,就可以了:create or replace trigger TRGDELETE_FILESHARE_FILEMARK
after delete on FILE_SHARE
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
delete from FILE_MARK where FM_SHARE_ID = :OLD.FS_ID;
commit;
end;
在执行的时候在delete from FILE_MARK where FM_SHARE_ID = :OLD.FS_ID;处提示等待资源时检测到死锁