如何在触发器打开和关闭时检查日志 [英] How to check the log when Trigger is turned On and Off

问题描述

亲爱的，

我创建了一个触发器，只要特定列发生变化，就会将条目插入到新表中。但是我收到了客户的投诉，称没有为日志表中的所有更新记录维护日志。我觉得可能是由于触发器在更新某些记录时被关闭所以它可能没有被记录在日志表中。



我想检查我的触发器是什么时候在SQL Server 2005数据库中打开和关闭。



SQL Server是否保留触发器开/关事件的日志？如何查看触发器是打开还是关闭的日志记录？



提前致谢。

解决方案

来自MS

 使用 tempdb; 
  go  
 创建  table  dbo.t（i  int  不  null ）; 
  go  
 创建 触发器<  insert  
  as  
 开始 
  return ; 
  end ; 
  go  
 创建 触发器 DdlTrigg  on   database  
 alter_table 
  as  
 开始 
 声明  @ e  xml = eventdata（）; 
 选择 ntvalue（' 名称[1 ]'，'  nvarchar（260）'） as  disabled_trigger 
 来自  @ e  .nodes（'  / EVENT_INSTANCE / AlterTableActionList / Disable / Triggers [1]'） as  n（t）
 选择 ntvalue（'  Name [1]'，'  nvarchar（260）'）< span class =code-keyword> as  enabled_trigger 
 来自  @ e  .nodes（'  / EVENT_INSTANCE / AlterTableActionLis t /启用/触发[1]'） as  n（t）
  return ; 
  end ; 
  go  
 
   -   尝试在某些表上禁用触发器： 
  alter   table  dbo。 t禁用触发器 ins_t; 
  alter   table  dbo.t enable  trigger  ins_t; 
  go  
  drop   table  dbo.t; 
  drop  触发器 DdlTrigg   数据库; 
  go  

来自https://connect.microsoft.com / SQLServer / feedbackdetail / view / 434951 / ddl-trigger-still-not-working-disable-trigger-is-this-not-an-event [ ^

Dear all,
I have created a trigger which inserts entry into new table whenever there is change in specific column. But I received a complaint from client saying that log is not maintained for all updated records in Log Table. I feel it may be due to trigger is turned off while updating some records so it may not have been recorded in log table.

I want to check when my trigger is turned On and Off in SQL Server 2005 database.

Does SQL Server maintain log of Trigger On/Off events? How to see log record of whether Trigger is Turned On or Off?

Thanks in advance.

解决方案

From MS

use tempdb;
go
create table dbo.t ( i int not null );
go
create trigger ins_t on dbo.t after insert
as
begin
return;
end;
go
create trigger DdlTrigg on database
after alter_table
as
begin
    declare @e xml = eventdata();
    select n.t.value('Name[1]', 'nvarchar(260)') as disabled_trigger
     from @e.nodes('/EVENT_INSTANCE/AlterTableActionList/Disable/Triggers[1]') as n(t)
    select n.t.value('Name[1]', 'nvarchar(260)') as enabled_trigger
     from @e.nodes('/EVENT_INSTANCE/AlterTableActionList/Enable/Triggers[1]') as n(t)
    return;
end;
go

-- Try disabling trigger on some tables:
alter table dbo.t disable trigger ins_t;
alter table dbo.t enable trigger ins_t;
go
drop table dbo.t;
drop trigger DdlTrigg on database;
go

From https://connect.microsoft.com/SQLServer/feedbackdetail/view/434951/ddl-trigger-still-not-working-disable-trigger-is-this-not-an-event[^]

这篇关于如何在触发器打开和关闭时检查日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！