March 21, 2019 at 6:23 am
I am been putting together some auditing in non-prod that logs to a table and sends out a email on any changes.I can get the logins and stored procedures to work, but I can't get the table trigger to work? After I create the table and trigger, I get the error below and I can't figure it out? I have tried put the SET options in several locations in the trigger.
Msg 1934, Level 16, State 1, Procedure Trg_TrackAuditManagement_TABLE, Line 32 [Batch Start Line 9]
SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Here is the code:
USE [DBA_DB]
GO
CREATE TABLE [dbo].[TrackAuditManagement](
[RowNum] [int] IDENTITY(1,1) NOT NULL,
[EventType] [nvarchar](100) NULL,
[EventTime] [datetime] NOT NULL,
[Servername] [nvarchar](100) NOT NULL,
[Command] [nvarchar](100) NOT NULL,
[ObjectName] [varchar](200) NOT NULL,
[ObjectType] [varchar](100) NOT NULL,
[DatabaseName] [varchar](100) NOT NULL,
[WhoDidIt] [varchar](100) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TrackAuditManagement] ADD CONSTRAINT [DF__TrackAudi__Event__173876EA] DEFAULT (getdate()) FOR [EventTime]
GO
CREATE Trigger [Trg_TrackAuditManagement_TABLE]
on ALL Server
for DDL_TABLE_EVENTS
as
set nocount on
declare @data xml,
@EventType varchar(100),
@EventTime datetime,
@ServerName varchar(100),
@CommandText varchar(100),
@ObjectName varchar(100),
@ObjectType varchar(100),
@DatabaseName varchar(100),
@WhoDidIt varchar(100),
@EmailSubject varchar(500),
@EmailBody varchar(800),
@EmailRecipients varchar(300)
set @EmailRecipients = 'YourEmail@domain.com'
set @data = eventdata()
set @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
set @EventTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
set @ServerName = @data.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(100)')
set @CommandText = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(100)')
set @ObjectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)')
set @ObjectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]','varchar(100)')
set @DatabaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(100)')
set @WhoDidIt = @data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(100)')
insert into DBmaint.dbo.TrackAuditManagement values
(@EventType,@EventTime,@ServerName,@CommandText,@ObjectName,@ObjectType,@DatabaseName, @WhoDidIt)
set @EmailSubject = 'ALERT: ' + @EventType + ' occurred by ' + @WhoDidIt + ' in ' + @DatabaseName + ' on ' + @ServerName
set @EmailBody = 'DDL_TABLE_Event: ' + @EventType + char(10) +
'Event Occured at: ' + convert(Varchar, @EventTime) + char(10) +
'ServerName: ' + @ServerName + char(10) +
'CommandText: ' + @CommandText + char(10) +
'Object Name: ' + @ObjectName + char(10) +
'Object Type: ' + @ObjectType + char(10) +
'Database: ' + @DatabaseName + char(10) +
'Who Did It: ' + @WhoDidIt
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'your mail profile',
@recipients = @EmailRecipients,
@subject = @EmailSubject,
@body = @EmailBody print @Eventtype + ' activity completed successfully.'
GO
ENABLE TRIGGER [Trg_TrackAuditManagement_TABLE] ON ALL SERVER
GO
March 21, 2019 at 8:22 am
i just appended this to your commands, and it worked fine: SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
CREATE TABLE [dbo].[TrackAuditManagement](
[RowNum] [int] IDENTITY(1,1) NOT NULL,
[EventType] [nvarchar](100) NULL,
[EventTime] [datetime] NOT NULL,
[Servername] [nvarchar](100) NOT NULL,
[Command] [nvarchar](100) NOT NULL,
[ObjectName] [varchar](200) NOT NULL,
[ObjectType] [varchar](100) NOT NULL,
[DatabaseName] [varchar](100) NOT NULL,
[WhoDidIt] [varchar](100) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TrackAuditManagement] ADD CONSTRAINT [DF__TrackAudi__Event__173876EA] DEFAULT (getdate()) FOR [EventTime]
GO
CREATE Trigger [Trg_TrackAuditManagement_TABLE]
on ALL Server
for DDL_TABLE_EVENTS
as
set nocount on
declare @data xml,
@EventType varchar(100),
@EventTime datetime,
@ServerName varchar(100),
@CommandText varchar(100),
@ObjectName varchar(100),
@ObjectType varchar(100),
@DatabaseName varchar(100),
@WhoDidIt varchar(100),
@EmailSubject varchar(500),
@EmailBody varchar(800),
@EmailRecipients varchar(300)
set @EmailRecipients = 'YourEmail@domain.com'
set @data = eventdata()
set @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
set @EventTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
set @ServerName = @data.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(100)')
set @CommandText = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(100)')
set @ObjectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)')
set @ObjectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]','varchar(100)')
set @DatabaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(100)')
set @WhoDidIt = @data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(100)')
insert into DBmaint.dbo.TrackAuditManagement values
(@EventType,@EventTime,@ServerName,@CommandText,@ObjectName,@ObjectType,@DatabaseName, @WhoDidIt)
set @EmailSubject = 'ALERT: ' + @EventType + ' occurred by ' + @WhoDidIt + ' in ' + @DatabaseName + ' on ' + @ServerName
set @EmailBody = 'DDL_TABLE_Event: ' + @EventType + char(10) +
'Event Occured at: ' + convert(Varchar, @EventTime) + char(10) +
'ServerName: ' + @ServerName + char(10) +
'CommandText: ' + @CommandText + char(10) +
'Object Name: ' + @ObjectName + char(10) +
'Object Type: ' + @ObjectType + char(10) +
'Database: ' + @DatabaseName + char(10) +
'Who Did It: ' + @WhoDidIt
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'your mail profile',
@recipients = @EmailRecipients,
@subject = @EmailSubject,
@body = @EmailBody print @Eventtype + ' activity completed successfully.'
GO
ENABLE TRIGGER [Trg_TrackAuditManagement_TABLE] ON ALL SERVER
GO
Lowell
March 21, 2019 at 8:32 am
Sorry..I should of put more detail. The table and trigger create fine, but when you try to create a new table the invokes the trigger is where is what brings up the error.
March 21, 2019 at 12:10 pm
Jasonb-231582 - Thursday, March 21, 2019 8:32 AMSorry..I should of put more detail. The table and trigger create fine, but when you try to create a new table the invokes the trigger is where is what brings up the error.
What were the settings that were used when you created the trigger? Those are stored with the trigger. You can check by executing the following in the database:SELECT uses_ansi_nulls, uses_quoted_identifier
FROM sys.sql_modules
WHERE object_id = object_id('Trg_TrackAuditManagement_TABLE')
Sue
March 21, 2019 at 12:42 pm
It comes up blank.
March 21, 2019 at 12:46 pm
Jasonb-231582 - Thursday, March 21, 2019 12:42 PMIt comes up blank.
Then you didn't run it in the database where that trigger lives or the trigger name really isn't Trg_TrackAuditManagement_TABLE
Sue
March 21, 2019 at 1:22 pm
i am guessing that maybe the trigger definition should have the ansi settings in it, right?CREATE Trigger [Trg_TrackAuditManagement_TABLE]
on ALL Server
for DDL_TABLE_EVENTS
as
SET NOCOUNT ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
.....
Lowell
March 21, 2019 at 1:59 pm
I tried that...what gets me is the stored procedure trigger works perfectly and the only thing different is DDL_PROCEDURE_EVENTS.
CREATE Trigger [Trg_TrackAuditManagement_PROCEDURE]
on ALL Server
for DDL_PROCEDURE_EVENTS
March 21, 2019 at 2:04 pm
Beginning to think this is a SQL bug with the DDL_TABLE_EVENTS? Hardly anyone uses these DDL triggers, so I can't find much documentation on it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply