删除会员所有信息的触发器

之前，在项目的管理员后台删除会员信息的时候，要求与会员相关的所有表信息删除。这里的删除，指的都是逻辑删除，即将记录的状态置为删除状态，不是从数据库直接删除的物理删除。尽管最后项目经理要求不采用触发器的方案，采用在程序端置状态的方式来实现，还是来mark一下，就算是对Sql server触发器复习一下吧。

该触发器的代码如下所示：

---在membership会员信息表里面删除指定membershipid的企业会员时(实际上没有进行物理删除，只是将status置为-1)
---将与企业会员有关的信息表里面的相关信息进行逻辑删除，即status置为-1
If Exists(Select 1 From sys.triggers Where name='membership_update')
    Drop  Trigger membership_update
Go
create trigger membership_update
on Membership
for update
as
begin
declare @membershipid as int,@status as int
select @membershipid=Membershipid,@status=Status from inserted;
if @status=-1
 begin
       update company set status=@status where membershipid=@membershipid;
       update supply set status=@status where membershipid=@membershipid;
       update buy set status=@status where membershipid=@membershipid;
       update companynews set status=@status where membershipid=@membershipid;
       update honor set status=@status where membershipid=@membershipid;
       update friendlink set status=@status where membershipid=@membershipid;
       update [message] set status=@status where membershipid=@membershipid;
       update employment set status=@status where membershipid=@membershipid;
       update notice set status=@status where membershipid=@membershipid;
 end
end