我用如下语句:
create trigger dbo.delete_leave on dbo.leave
for delete
as
delete from leave_m where exists( select * from deleted where leave_m.bill_id=deleted.id)
go来产生一个删除记录的事件触发,但是执行这个语句之后, 当leave表里头删除一条记录的时候leave_m里头的全部数据都删除了,而不是删除其中的一个记录,为什么?该如何才可以实现leave删除一条记录leave_m也删除一条记录?
create trigger dbo.delete_leave on dbo.leave
for delete
as
delete from leave_m where exists( select * from deleted where leave_m.bill_id=deleted.id)
go来产生一个删除记录的事件触发,但是执行这个语句之后, 当leave表里头删除一条记录的时候leave_m里头的全部数据都删除了,而不是删除其中的一个记录,为什么?该如何才可以实现leave删除一条记录leave_m也删除一条记录?
为什么?请高手指点!
上面的应该可以。楼主的exists所以不行是因为where子句恒成立。
对每条记录都是。
对于exists(select ...)来说,每句的时候都成立。而用where leave_m.bill_id= (select deleted.id from deleted )
或者leave_m.bill_id in (select deleted.id from deleted )
进行判断的话,则会过滤掉不符合条件的记录
for delete
as
delete from leave_m a where exists( select 1 from deleted where a.bill_id=id)
go