if exist (select d.state2 from deleted as d,oa_email as e where e.id=d.id and d.state2 <>'del')
这样我也试过,会报如下错误: Server: Msg 156, Level 15, State 1, Procedure p_email_del, Line 3 Incorrect syntax near the keyword 'select'. Server: Msg 170, Level 15, State 1, Procedure p_email_del, Line 3 Line 3: Incorrect syntax near ')'. =============================== 我想知道的是在DELETED表里有多行的时候, 我怎样才能得到符合特定条件的行??
呵呵,写错一个单词: exist 应该为exists.if exists (select d.state2 from deleted as d,oa_email as e where e.id=d.id and d.state2 <>'del') 另外,你用什么数据库?oracle 还是sql server 或别的?
to yangzi:谢谢提示我用的是sql server. ======================== to july:我想sql server应该支持BEFORE/AFTER delete吧(我不知道的^_^) 我的目的是:在删除一条记录的时候(在触发器里)检查state2的值,如果为'del'则删除 如果非则弹回删除事务,改为update oa_email set state1='del' from oa_email,deleted where oa_email.id=deleted.id ========================= 另外在影响多行记录的时候如何让触发器执行多次??
to yangzi:首先祝你情人节快乐!(如果还没有的话,就祝你早日找到个如花似玉的^_^) 对于你说的一二两点你能确定吗?? 如果改用存储过程的话又应该如何实现呢?? 我的delete语句是:delete oa_email where id=2 如果是:delete oa_email where id in (....)的话,用存储过程不是要建立循环吗? ==================== create trigger p_email_del on oa_email for delete as if exists (select d.state2 from deleted as d,oa_email as e where e.id=d.id and e.state2<>'del') begin rollback tran update oa_email set state1='del' from oa_email,deleted where oa_email.id=deleted.id end 上面这个TRIGGER在删除一条记录的时候执行正确,但在一次删除多条记录,且其中有一条记录的STATE2的值为‘DEL’时则把符合删除条件的多条记录全删了。 ====================== 所以我想要的是:在影响多行记录的时候如何让触发器执行多次??
Server: Msg 156, Level 15, State 1, Procedure p_email_del, Line 3
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Procedure p_email_del, Line 3
Line 3: Incorrect syntax near ')'.
===============================
我想知道的是在DELETED表里有多行的时候,
我怎样才能得到符合特定条件的行??
exist 应该为exists.if exists (select d.state2 from deleted as d,oa_email as e where e.id=d.id and d.state2 <>'del')
另外,你用什么数据库?oracle 还是sql server 或别的?
========================
to july:我想sql server应该支持BEFORE/AFTER delete吧(我不知道的^_^)
我的目的是:在删除一条记录的时候(在触发器里)检查state2的值,如果为'del'则删除
如果非则弹回删除事务,改为update oa_email set state1='del' from oa_email,deleted where oa_email.id=deleted.id
=========================
另外在影响多行记录的时候如何让触发器执行多次??
第二,对于sql server,如果你没有开始事务的话,不可能回滚事务,与oracle不同。
第三,你的select 语句也有问题,不好意思,上次我没有仔细看。
第三,建议你使用存储过程来删除数据。能贴出你的delete语句来吗?
对于你说的一二两点你能确定吗??
如果改用存储过程的话又应该如何实现呢??
我的delete语句是:delete oa_email where id=2
如果是:delete oa_email where id in (....)的话,用存储过程不是要建立循环吗?
====================
create trigger p_email_del on oa_email for delete as
if exists (select d.state2 from deleted as d,oa_email as e where e.id=d.id and e.state2<>'del')
begin
rollback tran
update oa_email set state1='del' from oa_email,deleted where oa_email.id=deleted.id
end
上面这个TRIGGER在删除一条记录的时候执行正确,但在一次删除多条记录,且其中有一条记录的STATE2的值为‘DEL’时则把符合删除条件的多条记录全删了。
======================
所以我想要的是:在影响多行记录的时候如何让触发器执行多次??
insert into oa_email (field1,...,state1,...fieldn)
select field1,...,'del',...fieldn from deleted where state2<>'del'return
oa_email表里有id,addressee,addresser,state1,state2,当收件人在删除一条记录的时候先检查state2,如果state2的值为‘del’则删除,也就是说发件人也把该邮件删除了则删除,否则把state1的值改为‘del’。反过来当发件人删除的时候先检查state1,这样就不会留下收件人和发件人都删除了的垃圾邮件。
=====================
Happy Saint Valentine's Day!