这个触发器相对简单了create trigger tr_SMS_Update on SMS for update as update U set SendSMS=1 from User U,inserted i where u.id=i.UserId and not exists ( select 1 from SMS where UserId=u.id and send=0 )
还要删除create trigger tr_SMS_Update on SMS for update as update U set SendSMS=1 from User U,inserted i where u.id=i.UserId and not exists ( select 1 from SMS where UserId=u.id and send=0 )delete S from SMS s,inserted i where s.UserId=i.UserId and not exists ( select 1 from SMS where UserId=i.id and send=0 )
hellowork(一两清风) ( BEGIN TRANSACTION --更新用户表 UPDATE a set SendSMS = 1 FROM tbUser as a INNER JOIN inserted as i on a.ID = i.UserID WHERE NOT EXISTS(select 1 from tbSMS where UserID = i.UserID and Send = 0 ) IF @@error <> 0 ROLLBACK TRANSACTION ELSE这里好像少判断了:当同一个UserID 的Send都为1的时候谢谢
INNER JOIN inserted as i on a.ID = i.UserID WHERE NOT EXISTS(select 1 from tbSMS where UserID = i.UserID and Send = 0 ) -------------------------------------------------------------------------------- 这个就是判断.尤其是: NOT EXISTS(select 1 from tbSMS where UserID = i.UserID and Send = 0 )
on SMS
for update
as
update U
set SendSMS=1
from User U,inserted i
where u.id=i.UserId
and not exists (
select 1 from SMS
where UserId=u.id
and send=0
)
on SMS
for update
as
update U
set SendSMS=1
from User U,inserted i
where u.id=i.UserId
and not exists (
select 1 from SMS
where UserId=u.id
and send=0
)delete S
from SMS s,inserted i
where s.UserId=i.UserId
and not exists (
select 1 from SMS
where UserId=i.id
and send=0
)
BEGIN TRANSACTION
--更新用户表
UPDATE a set SendSMS = 1
FROM tbUser as a
INNER JOIN inserted as i on a.ID = i.UserID
WHERE NOT EXISTS(select 1 from tbSMS where UserID = i.UserID and Send = 0 )
IF @@error <> 0
ROLLBACK TRANSACTION
ELSE这里好像少判断了:当同一个UserID 的Send都为1的时候谢谢
WHERE NOT EXISTS(select 1 from tbSMS where UserID = i.UserID and Send = 0 )
--------------------------------------------------------------------------------
这个就是判断.尤其是:
NOT EXISTS(select 1 from tbSMS where UserID = i.UserID and Send = 0 )
单学C#还是不行的啊谢谢hellowork(一两清风)