DDL Database Trigger causes error when Droping Table
-
I have a Database Trigger as defined below.
WHen I try and Drop a Table I get an error.
Drop failed for Table 'dbo.abc.
An exception occurred while executing a Transact-SQL statement or batch.
INSERT Failed because the following 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)
CREATE TRIGGER Backup_Procs
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
SET NOCOUNT ON
DECLARE @Data XML
SET @Data = EVENTDATA()
INSERT INTO dbo.changelog(databasename, eventtype,
objectname, objecttype, sqlcommand, loginname)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
GO
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Welsh Corgi (11/29/2011)
I have a Database Trigger as defined below.WHen I try and Drop a Table I get an error.
Drop failed for Table 'dbo.abc.
CREATE TRIGGER Backup_Procs
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
Your trigger is related to procedures.
I don't think it is causing this issue when you delete a table.
Do you have similar trigger for table as well?
-
Yes, I have a trigger for Insert, Update & Delete Table.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
not sure if it's applicable, as I'm not sure of the specific error you were getting, but you have a logging table dbo.changelog;;
your trigger could potentially fail if the person who created/altered the proc did not have INSERT rights to that table; did you grant INSERTon dbo.changelog TO PUBLIC, or some other role you know everyone would be a part of?
i trip over something very similar with server wide DDL triggers, and have to do that additional permissions granting to make sure the trigger doesn't fail.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
It fails for people with sysadmin.
When I initially created it I could add tables.
Then I tried dropping a table and I got the same error.
The following day I get an error when attempting to create a table (sysadmin).
Disable the trigger and I can create the table.
It may have something to do with the XML and/or ANSI NUll Setting.
You can get most of the information through a INFORMATION SCHEMA Table but I can't remember the name of the DMV.:unsure:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
I shared following with you yesterday. Didn't it help?
-
Because it's using an XML data type in the trigger, it has to have the correct settings for ANSI NULLs, ARITHABORT, and a few others. They're the ones applicable to indexed views, and details can be looked up there.
Are you issuing DDL commands via scripts in SSMS connection windows, or are you using the "New Table" wizard and that kind of thing? The connection defaults are different for those, and you need to make sure the connection has the right settings. The connection defaults are set at the server level, or you can set them for the specific connection if you issue your DDL commands via scripts in SSMS.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
@GSquared, I just created the Triggers & Table in T-SQL.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Dev (12/1/2011)
I shared following with you yesterday. Didn't it help?Unfortunately it did not for the article was related to an issue with an Indexed View. Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Welsh Corgi (12/1/2011)
Dev (12/1/2011)
I shared following with you yesterday. Didn't it help?Unfortunately it did not for the article was related to an issue with an Indexed View. Thanks.
I am not sure but if it checks for dependent objects (triggers / views etc) then it might be the same. I would like you to investigate on following.
"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"
-
Welsh Corgi (12/1/2011)
@GSquared, I just created the Triggers & Table in T-SQL.Thanks.
What are the connection settings you're using when you issue the create table command?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
GSquared (12/1/2011)
Welsh Corgi (12/1/2011)
@GSquared, I just created the Triggers & Table in T-SQL.Thanks.
What are the connection settings you're using when you issue the create table command?
There are no connection strings involved. I created it in using T-SQL.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
go to tools options...
i'm guessing the default settings on your specific connection do not have the same settings as mine, for example:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks Lowell. 😎
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Welsh Corgi (12/1/2011)
GSquared (12/1/2011)
Welsh Corgi (12/1/2011)
@GSquared, I just created the Triggers & Table in T-SQL.Thanks.
What are the connection settings you're using when you issue the create table command?
There are no connection strings involved. I created it in using T-SQL.
Not a connection string. Sorry that wasn't clear.
You can either change the settings in SSMS options (as suggested), or the script itself can have options like:
Use [MyDatabase]GO
SET ANSI_NULLS ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ARITHABORT ON;
SET ANSI_PADDING ON;
GO
CREATE TABLE dbo.MyTable ( ....
That's what I mean by connection settings in the script.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply