CREATE TRIGGER Delete_Account ON 用户表 FOR DELETE AS if not exists(select * from 帐号表 where 帐号 in (select 帐号 from deleted) and 用户<>deleted.用户) delete from 帐号表 where 帐号 = deleted.帐号
create table 用户表(account_id int) insert into 帐号表 select 1 insert into 帐号表 select 2 insert into 帐号表 select 3 insert into 用户表 select 1 insert into 用户表 select 2 insert into 用户表 select 2 goDELETE FROM 帐号表 WHERE (select count(*) from 帐号表 as a join 用户表 as b on a.account_id=b.account_id)=0if @@rowcount>0 print 'del ok'
你用触发器来删除就好了 CREATE TRIGGER test ON 用户表 FOR DELETE AS IF NOT EXISTS(SELECT 1 FROM 用户表 WHERE account_id IN (select account_id FROM DELETED) DELETE FROM 帐号表 WHERE account_id IN (select DISTINCT account_id FROM DELETED)
用触发器是最简便的了!如果不用触发器的话可以这样: DELETE FROM 帐号表 WHERE (select count(*) from 用户表 as where account_id=刚删除用户对应的帐号)=0 and account_id=刚删除用户对应的帐号
ON 用户表
FOR DELETE
AS
if not exists(select * from 帐号表 where 帐号 in (select 帐号 from deleted) and 用户<>deleted.用户)
delete from 帐号表 where 帐号 = deleted.帐号
insert into 帐号表 select 1
insert into 帐号表 select 2
insert into 帐号表 select 3
insert into 用户表 select 1
insert into 用户表 select 2
insert into 用户表 select 2
goDELETE FROM 帐号表
WHERE (select count(*) from 帐号表 as a join 用户表 as b on a.account_id=b.account_id)=0if @@rowcount>0
print 'del ok'
你用触发器来删除就好了
CREATE TRIGGER test ON 用户表
FOR DELETE
AS
IF NOT EXISTS(SELECT 1 FROM 用户表
WHERE account_id IN (select account_id FROM DELETED)
DELETE FROM 帐号表
WHERE account_id IN (select DISTINCT account_id FROM DELETED)
DELETE FROM 帐号表
WHERE (select count(*) from 用户表 as
where account_id=刚删除用户对应的帐号)=0
and
account_id=刚删除用户对应的帐号
如:
帐号表account:
字段 account_id用户表:user
字段 user_id
字段 account_id
字段 master如果master=1,则视为主用户
如果master=0,则视为副用户
删除副用户不影响帐号记录
但如果删除主用户,则将删除相关的帐号及所有副用户所以在我删除用户表记录时,只是先删除master=0的记录然后再删除帐号表中记录.
那些master=1的记录是通过帐号表的delete触发器来做的
删除掉某条帐号时,触发器将删除与之相关连的用户表的记录.我现在头痛的地方就是在删除掉满足条件的用户表中记录时.
除了查询到所有和其相关连的account_id还需要在不满足条件的用户表记录中看是否存在此account_id中对应的用户表记录
如果存在且其master=1,则在欲删除的帐号记录中剔除掉那条记录不知这样说的清不清楚,:)
问题已经解决了.先
delete掉欲删除用户表中master=0的记录
再
delete帐号表中account_id in(欲删除的用户表中的account_id)帐号中的delete触发器会删除相关连的用户表记录谢谢大家