Click here to Skip to main content
15,886,664 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 8-Sep-14 18:57pm
v3
Comments
Abdul Samad KP 9-Sep-14 2:03am    
How you are inserting updated values into the log table?
I mean, if the user is updating multiple records at the same time, then the trigger will be executed only once and all the new records will be there in the inserted table. So your insert query should insert all records available in the Inserted table.
This is important if you are processing (like inner joining with some other tables or getting value into parameters and doing some calculation or checking some conditions) the records in the Inserted table before inserting into the log table

1 solution

From MS

SQL
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[^]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900