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

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

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

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

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

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

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

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

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

复制代码 4: Drop Trigger membership_update

1
   4:     Drop  Trigger membership_update

复制代码 5: Go

1
   5: Go

复制代码 6: create trigger membership_update

1
   6: create trigger membership_update

复制代码 7: on Membership

1
   7: on Membership

复制代码 8: for update

1
   8: for update

复制代码 9: as

1
   9: as

复制代码 10: begin

1
  10: begin

复制代码 11: declare @membershipid as int,@status as int

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

复制代码 12: select @membershipid=Membershipid,@status=Status from inserted;

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

复制代码 13: if @status=-1

1
  13: if @status=-1

复制代码 14: begin

1
  14:  begin

复制代码 15: update company set status=@status where membershipid=@membershipid;

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

复制代码 16: update supply set status=@status where membershipid=@membershipid;

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

复制代码 17: update buy set status=@status where membershipid=@membershipid;

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

复制代码 18: update companynews set status=@status where membershipid=@membershipid;

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

复制代码 19: update honor set status=@status where membershipid=@membershipid;

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

复制代码 20: update friendlink set status=@status where membershipid=@membershipid;

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

复制代码 21: update [message] set status=@status where membershipid=@membershipid;

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

复制代码 22: update employment set status=@status where membershipid=@membershipid;

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

复制代码 23: update notice set status=@status where membershipid=@membershipid;

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

复制代码 24: end

1
  24:  end

复制代码 25: end

1
  25: end