单个触发器判断Insert/Delete/Update操

 

问题已解决，分享之：
插入操作（Insert）：Inserted表有数据，Deleted表无数据
删除操作（Delete）：Inserted表无数据，Deleted表有数据
更新操作（Update）：Inserted表有数据（新数据），Deleted表有数据（旧数据）


if exists(select 1 from inserted) and exists(select 1 from inserted) --更新
【update tb_search】 
else if exists(select 1 from inserted) --插入  
【inserted tb_search】
else --删除
【delete tb_search】

感谢各位热情讨论！
阿里噶多

ALTER TRIGGER [triger_Video]
  ON [dbo].[tb_Video]
  FOR INSERT,DELETE,UPDATE
  AS
BEGIN
  IF EXISTS(SELECT 1 FROM deleted) AND EXISTS(SELECT 1 FROM inserted)
  BEGIN
  UPDATE sqlstatement
  END
  ELSE IF EXISTS(SELECT 1 FROM inserted) 
  BEGIN
  INSERT sqlstatement
  END  
  ELSE
  BEGIN
  DELETE sqlstatement
  END
END

 

现在项目经理要求必须写在一起

SQL code

   
   

    
    ALTER TRIGGER [triger_Video]
  ON [dbo].[tb_Video]
  FOR INSERT,DELETE,UPDATE
  AS
BEGIN
  IF EXISTS(SELECT 1 FROM deleted) AND EXISTS(SELECT 1 FROM inserted)
  BEGIN
  UPDATE sqlstatement
  END
  ELSE IF EXISTS(SELECT 1 FROM inserted)  
  BEGIN
  INSERT sqlstatement
  END   
  ELSE
  BEGIN
  DELETE sqlstatement
  END
END
   
   

 

 

 

------------------------------------------------------------------

SQL code

  
  

   
   

   
   create trigger triger_Video on
   
    tb_Video
for insert,update,delete
   
   

   
   as
   
   

   
   if exists(select 1 from inserted )and not exists(select 1 from
   
    deleted)
 insert tb_Search select  UserId,Id,Title from inserted  --如果表 tb_Search中的id是自增列
   
   

   
   else
   
    
 if not exists(select 1 from
   
    deleted)
    update
   
    tb_Search 
    set TableName=i.UserId, Title=
   
   i.title
    from
   
    tb_Search a,inserted i
    where a.SourceId=
   
   a.id 

 else
   
    
    delete
   
    tb_Search 
    from
   
    tb_Search a,deleted d
    where  a.SourceId=
   
   d.id 

go
   
   

  
  

 

-----------------------------------------------------------

SQL code

  
  

   
   

   
   create trigger triger_Video on
   
    tb_Video
for insert,update,delete
   
   

   
   as
   
   

   
   if exists(select 1 from inserted )and not exists(select 1 from
   
    deleted)
 insert tb_Search select  UserId,Id,Title from inserted  --如果表 tb_Search中的id是自增列
   
   

   
   else
   
    
 if not exists(select 1 from
   
    deleted)
    update
   
    tb_Search 
    set TableName=i.UserId, Title=
   
   i.title
    from
   
    tb_Search a,inserted i
    where a.SourceId=
   
   a.id 

 else
   
    
    delete
   
    tb_Search 
    from
   
    tb_Search a,deleted d
    where  a.SourceId=
   
   d.id 

go
   
   


  
  

 

http://topic.csdn.net/u/20090921/16/1bc89a33-8aef-40e3-9285-34f8ba7b1f45.html