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