触发器—循环删除小例子

A表中删除一条记录，B表根据A表删除的记录中的某个字段的数据相应删除相关记录。

--触发器

create or replace trigger deleteHrPost after delete on hr_post for each row;

declare 

pragma autonomous_transaction;  --自治事务

begin

deleteHrPost(C1  varchar2(32));--调存储过程

commit;  --手动提交

end;

--存储过程

create or replace procedure deleteHrPost(C1 varchar2) is 

post_id_row varchar2(32);--记录B表当前需要删除记录的id

cursor posts_id(C2 varchar2) is select rule_id from jc_pub_user_role_test where post_code =C1;  --声明光标 

begin

open posts_id(C1);

fetch posts_id into post_id_row;

while posts_id%found loop   --开启循环

delete from jc_pub_user_role_test where rule_id = post_id_row ;

fetch posts_id into post_id_row;

end loop;  -- 关闭循环

end;   -- 一个end即可 包括了关闭光标