觸發器定義

if object_id('Tri_Test')is not null drop trigger Tri_Test
go
create trigger Tri_Test on REQ_HEAD for insert,update
as
if not exists(select 1 from deleted)
   insert REQ_HEAD_Log select Pkey, REFNO,CUST,RMANO,PURPOSE,QTY,REQDAT,REFFileName,CUST2,REGION,Plant,'insert',getdate()from inserted
else
  begin
    declare @Pkey int, @REFNO nvarchar(20),@CUST nvarchar(3) ,@RMANO nvarchar(10),@PURPOSE nvarchar(1),@QTY nvarchar(5),@REQDAT smalldatetime,@REFFileName nvarchar(50),@CUST2 nvarchar(3),@REGION nvarchar(1),@Plant nvarchar(1)
    select @Pkey=Pkey, @REFNO=REFNO,@CUST=CUST,@RMANO=RMANO,@PURPOSE=PURPOSE,@QTY=QTY,@REQDAT=REQDAT,@REFFileName=REFFileName,@CUST2=CUST2,@REGION=REGION,@Plant=Plant from deleted
    insert REQ_HEAD_Log select @Pkey,@REFNO,@CUST,@RMANO,@PURPOSE,@QTY,@REQDAT,@REFFileName,@CUST2,@REGION,@Plant,'update',getdate()from inserted
 end
go

if object_id('Tri_REQ_HEAD')is not null drop trigger Tri_REQ_HEAD
go
create trigger Tri_REQ_HEAD on REQ_HEAD for insert,update
as
if not exists(select 1 from deleted)
   insert REQ_HEAD_Log select Pkey, '',REFNO,'',CUST,'',RMANO,'',PURPOSE,'',QTY,'',REQDAT,'',REFFileName,'',CUST2,'',REGION,'',Plant,CreateBy,'insert',getdate()from inserted
else
  begin
    declare @Pkey int, @REFNO nvarchar(20),@CUST nvarchar(3) ,@RMANO nvarchar(10),@PURPOSE nvarchar(1),@QTY nvarchar(5),@REQDAT smalldatetime,@REFFileName nvarchar(50),@CUST2 nvarchar(3),@REGION nvarchar(1),@Plant nvarchar(1),@CreateBy nvarchar(50)
    select @Pkey=Pkey, @REFNO=REFNO,@CUST=CUST,@RMANO=RMANO,@PURPOSE=PURPOSE,@QTY=QTY,@REQDAT=REQDAT,@REFFileName=REFFileName,@CUST2=CUST2,@REGION=REGION,@Plant=Plant,@CreateBy=CreateBy from deleted
    insert REQ_HEAD_Log select @Pkey,REFNO,@REFNO,CUST,@CUST,RMANO,@RMANO,PURPOSE,@PURPOSE,QTY,@QTY,REQDAT,@REQDAT,REFFileName,@REFFileName,CUST2,@CUST2,REGION,@REGION,Plant,@Plant,@CreateBy,'update',getdate()from inserted
 end
go