我想实现的是,当mail_send表中增加一条记录时将该记录插入mail_send_add
当mail_send表中删除一条记录时将该记录插入mail_send_delete现在是mail_send_add可以实现,但mail_send_delete实现不了,大家帮我看看是什么原因啊?触发器代码如下
create or replace trigger tri_mail_send
after insert or delete on mail_send
for each row
begin
if inserting then
insert into mail_send_add (mail_id,mail_from_id,mail_to_id,mail_content,mail_send_time,read_flag,garbage_flag,download_url,mail_title,file_name,need_response)
values(:new.mail_id,:new.mail_from_id,:new.mail_to_id,:new.mail_content,:new.mail_send_time,:new.read_flag,:new.garbage_flag,:new.download_url,:new.mail_title,:new.file_name,:new.need_response);
end if;
if deleting then
insert into mail_send_delete(mail_id,mail_from_id,mail_to_id,mail_content,mail_send_time,read_flag,garbage_flag,download_url,mail_title,file_name,need_response)
values(:old.mail_id,:old.mail_from_id,:old.mail_to_id,:old.mail_content,:old.mail_send_time,:old.read_flag,:old.garbage_flag,:old.download_url,:old.mail_title,:old.file_name,:old.need_response);
end if;
end tri_mail_send;
当mail_send表中删除一条记录时将该记录插入mail_send_delete现在是mail_send_add可以实现,但mail_send_delete实现不了,大家帮我看看是什么原因啊?触发器代码如下
create or replace trigger tri_mail_send
after insert or delete on mail_send
for each row
begin
if inserting then
insert into mail_send_add (mail_id,mail_from_id,mail_to_id,mail_content,mail_send_time,read_flag,garbage_flag,download_url,mail_title,file_name,need_response)
values(:new.mail_id,:new.mail_from_id,:new.mail_to_id,:new.mail_content,:new.mail_send_time,:new.read_flag,:new.garbage_flag,:new.download_url,:new.mail_title,:new.file_name,:new.need_response);
end if;
if deleting then
insert into mail_send_delete(mail_id,mail_from_id,mail_to_id,mail_content,mail_send_time,read_flag,garbage_flag,download_url,mail_title,file_name,need_response)
values(:old.mail_id,:old.mail_from_id,:old.mail_to_id,:old.mail_content,:old.mail_send_time,:old.read_flag,:old.garbage_flag,:old.download_url,:old.mail_title,:old.file_name,:old.need_response);
end if;
end tri_mail_send;
在before insert触发器中,可以实现对本表的访问;
在after insert触发器中,不能实现对本表的访问;
在before/after update/delete触发器中,都不能实现对本表的访问 其实原因很简单,就是为了防止脏读
Oracle/PLSQL: BEFORE DELETE Trigger
after 改成 before