触发器学习

判断执行类型和执行的sql语句

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 建跟踪触发器
ALTER trigger [dbo].[tr_t02kc]
on [dbo].[T02KC] after update,insert,delete
as
begin
   declare @di table(et varchar(200),pt varchar(200),ei varchar(max))
   insert into @di exec('dbcc inputbuffer(@@spid)')
   
   declare @op varchar(10)
   select @op=case when exists(select 1 from inserted) and exists(select 1 from deleted)
                   then 'Update'
                   when exists(select 1 from inserted) and not exists(select 1 from deleted)
                   then 'Insert'
                   when not exists(select 1 from inserted) and exists(select 1 from deleted)
                   then 'Delete' end
                   
   if @op in('Update','Insert')
   begin
   insert into t02kc_log_sto
     (operate,yp_bm,yp_ph,kc_old,kc_new,price_old,price_new,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
     select @op,n.yp_bm,n.yp_ph,o.kc_sl,n.kc_sl,o.F_J_Price,n.F_J_Price,@@spid,
       (select login_name from sys.dm_exec_sessions where session_id=@@spid),
       (select program_name from sys.dm_exec_sessions where session_id=@@spid),
       (select hostname from sys.sysprocesses where spid=@@spid),
       (select client_net_address from sys.dm_exec_connections where session_id=@@spid),
       (select top 1 isnull(ei,'') from @di),
       getdate()
     from inserted n
     left join deleted o on o.YP_BM=n.yp_bm and o.YP_ph=n.yp_ph
   end
   else
   begin
     insert into t02kc_log_sto
       (operate,yp_bm,yp_ph,kc_old,kc_new,price_old,price_new,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
       select @op,o.yp_bm,o.YP_ph,o.kc_sl,null,o.F_J_Price,null,@@spid,
         (select login_name from sys.dm_exec_sessions where session_id=@@spid),
         (select program_name from sys.dm_exec_sessions where session_id=@@spid),
         (select hostname from sys.sysprocesses where spid=@@spid),
         (select client_net_address from sys.dm_exec_connections where session_id=@@spid),
         (select top 1 isnull(ei,'') from @di),
         getdate()
       from deleted o
   end
end