在Sql Server触发器中判断操作是Insert还是Update还是Delete

　　1.插入操作（Insert）

　　Inserted表有数据，Deleted表无数据

 

　　2.删除操作（Delete）

　　Inserted表无数据，Deleted表有数据

 

　　3.更新操作（Update）

　　Inserted表有数据（新数据），Deleted表有数据（旧数据）

 

DECLARE
    @IsInsert bit,
    @IsUpdate bit,
    @IsDelete bit

IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
    SET @IsInsert = 1
ELSE
    SET @IsInsert = 0

IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
    SET @IsUpdate = 1
ELSE
    SET @IsUpdate = 0

IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
    SET @IsDelete = 1
ELSE
    SET @IsDelete = 0 

 

 

 

if   not   exists (select   1   from   inserted) 
  begin
   delete from SFIS.dbo.QaDefectFinal where DefectDataNo in (select DefectDataNo from deleted)
  end
 else if not exists (select 1 from deleted)
  insert into SFIS.dbo.QaDefectFinal (DefectDataNo,DefectMainNo,FactNo,DefectType,classno,PartNumber,
  Erea,DefectName,CreateDate,CreateBy,RCreateDate,ItemColor)
  select DefectDataNo,DefectMainNo,'一厂',DefectType,classno,PartNumber,Erea,DefectName,
  convert(varchar,CreateDate,112) CreateDate,CreateBy,RCreateDate,ItemColor
  from inserted
 else
  update SFIS.dbo.QaDefectFinal set DefectMainNo=inserted.DefectMainNo,DefectType=inserted.DefectType,
  classno=inserted.classno,PartNumber=inserted.PartNumber,Erea=inserted.Erea,
  DefectName=inserted.DefectName,CreateDate=convert(varchar,inserted.CreateDate,112),
  CreateBy=inserted.CreateBy,RCreateDate=inserted.RCreateDate,ItemColor=inserted.ItemColor
  from SFIS.dbo.QaDefectFinal,inserted
  where SFIS.dbo.QaDefectFinal.DefectDataNo=inserted.DefectDataNo