I have a DDL Trigger that writes a record into a table when any DDL event happens.
It works great except when I try dropping any object. I get an error at that point and the object is not dropped. The trigger and error are listed below.
Thanks.
J
Trigger:
/****** Object: DdlTrigger [MonitorDBChanges] Script Date: 07/24/2007 11:02:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [MonitorDBChanges]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
set nocount on
declare @.EventType varchar(100)
declare @.SchemaName varchar(100)
declare @.ObjectName varchar(100)
declare @.ObjectType varchar(100)
declare @.Script varchar(2000)
SELECT
@.EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')
,@.SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')
,@.ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
,@.ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')
,@.Script = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
-- Is the default schema used
if @.SchemaName = ' ' select @.SchemaName = default_schema_name from sys.sysusers u join sys.database_principals p
on u.uid = p.principal_id where u.name = CURRENT_USER
insert into MonitorDBChanges
select @.EventType, @.SchemaName, @.ObjectName, @.ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN(),@.Script
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [MonitorDBChanges] ON DATABASE
*******************************************************************************
Error Msg:
SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (Microsoft SQL Server, Error: 1934)
Have you tried placing SET ARITHABORT ON along with the ANSI_NULLS and QUOTED_IDENTIFIER options?
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Tried that but I am going to give it another try.|||Confirmed. No difference
Any other ideas?
