2006/05/10 | 没有办法还得捡起SQLServer来用-触发器
类别(数据库[原创]) | 评论(0) | 阅读(51) | 发表于 15:38
------------------------------------------- 1 -------------------------------------------
USE qzeip_ec
DROP TRIGGER ent_user_update
GO
CREATE TRIGGER ent_user_update ON ec_ent_user
after update,insert
as
declare
@updatecount Int,
@sycount int,
@userid Char(36)
begin
  BEGIN Tran T1
  Set @userid = (select name from inserted)
  Set @updatecount = (Select count(*) From sy_user where sy_user.userid = @userid)
  if @updatecount > 0
    begin
    update sy_user set password = ins.password,username = ins.contman from inserted ins where sy_user.userid = ins.name    
    end
  else
    begin
    insert into sy_user(userid,password,username)SELECT ins.name,ins.password,ins.contman FROM inserted ins
    end
If @@Error = 0
begin
Set @updatecount = (Select count(*) From sy_user where sy_user.userid = @userid)
if @updatecount > 1
Begin
   Rollback Tran T1
Raiserror('该用户ID已经存在',16,1)
End
else
  begin
Commit Tran
  end
end
Else
Begin
  Rollback Tran T1
  Raiserror('修改失败',16,1)
End
  
  
end
GO
------------------------------------------- 2 -------------------------------------------
USE qzeip_ec
DROP TRIGGER ent_user_delete
GO
CREATE TRIGGER ent_user_delete ON ec_ent_user
after delete
as
begin
  delete from sy_user where userid in (select del.name from deleted del)
end
GO
0

评论Comments

日志分类
首页[148]
小新的博客[原创][70]
我的文档[原创][33]
杂项[5]
企业信息化[9]
数据库[原创][12]
文档转载[17]
配置管理[2]