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

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

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

   1: ---在membership会员信息表里面删除指定membershipid的企业会员时(实际上没有进行物理删除，只是将status置为-1)

   2: ---将与企业会员有关的信息表里面的相关信息进行逻辑删除，即status置为-1

   3: If Exists(Select 1 From sys.triggers Where name='membership_update')

   4:     Drop  Trigger membership_update 

   5: Go

   6: create trigger membership_update

   7: on Membership

   8: for update

   9: as

  10: begin

  11: declare @membershipid as int,@status as int

  12: select @membershipid=Membershipid,@status=Status from inserted;

  13: if @status=-1

  14:  begin

  15:        update company set status=@status where membershipid=@membershipid;

  16:        update supply set status=@status where membershipid=@membershipid;

  17:        update buy set status=@status where membershipid=@membershipid;

  18:        update companynews set status=@status where membershipid=@membershipid;

  19:        update honor set status=@status where membershipid=@membershipid;

  20:        update friendlink set status=@status where membershipid=@membershipid;

  21:        update [message] set status=@status where membershipid=@membershipid;

  22:        update employment set status=@status where membershipid=@membershipid;

  23:        update notice set status=@status where membershipid=@membershipid;

  24:  end

  25: end

转载于:https://www.cnblogs.com/jerry01/archive/2012/09/13/2683050.html