Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Monitoring Changes in Your Database Using DDL Triggers Expand / Collapse
Author
Message
Posted Monday, October 6, 2008 9:36 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
I'm seeing something interesting, and was wondering if someone can tell me what's going on. As an experiment to see what would appear in the log, I modify a table called PhoneList to add one character to a name field and I see the following sequence in my audit table:

17  ALTER TABLE dbo.PhoneList DROP CONSTRAINT FK_...
18 CREATE TABLE dbo.Tmp_PhoneList ...
19 GRANT SELECT on dbo.Tmp_PhoneList To Public as dbo
20 DROP TABLE dbo.PhoneList
21 CREATE CLUSTERED INDEX IX_PhoneList ON dbo.PhoneList ...
22 ALTER TABLE dbo.PhoneList ADD CONSTRAINT FK_...
23 CREATE TRIGGER tr_PhoneList ON dbo.PhoneList FOR ...

There has to be an implied 20.5 where the Tmp_PhoneList table is renamed to PhoneList, but the trigger doesn't catch it.

Another interesting thing is the dialog box in SSMS is saying that it will be saving the table referenced in the FK, but the trigger is not catching that. I'm assuming there that SSMS is initially thinking that the FK table needs to be saved, but when it comes time to run the query, the database engine realizes that it doesn't have to be and doesn't do anything.


Any thoughts about the missing rename? Is somehow the audit trigger missing it?
Post #581174
Posted Monday, October 6, 2008 10:59 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 29, 2014 7:01 AM
Points: 1,571, Visits: 1,852
Yes, I noticed the same thing when I tested the audit program on a test database. I don't know why the "rename" step (or whatever the system REALLY does) is not in the audit.
However, I was a little disappointed to see that what I THOUGHT would be an audit of a column change turned out to be an audit of a change to the table.

While I can understand that column changes might happen by a different route than what I would have thought, column changes are the type of audit that I really wanted to track, so this method turns out to not help me much.
Post #581257
Posted Monday, October 6, 2008 11:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
Carla, have you considered experimenting with a trigger on the information_schema.columns view? BOL says you can apply triggers to views, but I don't know if you can bind them to system views.
Post #581290
Posted Tuesday, October 7, 2008 12:59 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:55 AM
Points: 86, Visits: 478
The attached code works beautifully, except we find that we can no longer use "SET ANSI_PADDING OFF" and must convert to "SET ANSI_PADDING ON", see error message below.

I like to understand why would this be the case?

thanks!

error message:
Msg 1934, Level 16, State 1, Procedure DDLChange_trg, Line 29

INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. 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.


Post #582110
Posted Tuesday, October 7, 2008 1:41 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 29, 2014 7:01 AM
Points: 1,571, Visits: 1,852
Pieter,
The Error message implies that you can't use SET ANSI_PADDING OFF with XML data methods, and this script uses an XML datatype, but I couldn't find a good explanation in BOL.

Wayne,
Wayne West (10/3/2008)
If you wanted to live dangerously...
I wouldn't recommend it because I'm not comfortable doing anything to system objects.
...
Oh, what the heck. I just installed SQL Server 2008 on my MacBook Pro, maybe I'll install 2000 on it tonight and play with system object triggers....


I thought YOU were the one who was going to live dangerously! ;) - - I have not been bold enough to test this!
Post #582148
Posted Tuesday, October 7, 2008 2:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:55 AM
Points: 86, Visits: 478
thanks Wayne!
We do data warehousing instead of OLTP and like the space savings associated with "set ansi_padding off". Is there a way that we can make it work and have the audit trail?

Thanks,
Piet

Post #582161
Posted Tuesday, October 7, 2008 2:17 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
Pieter (10/7/2008)
thanks Wayne!
We do data warehousing instead of OLTP and like the space savings associated with "set ansi_padding off". Is there a way that we can make it work and have the audit trail?

Thanks,
Piet

Sorry, can't help you there, Piet. I'm strictly an OLTP guy, I don't work with DW at all.

Myself, I much prefer ANSI padding off, I hate having to trim fields before concatenating.
Post #582171
Posted Tuesday, October 7, 2008 2:58 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
Carla Wilson (10/7/2008)

Wayne West (10/3/2008)
If you wanted to live dangerously...
I wouldn't recommend it because I'm not comfortable doing anything to system objects.
...
Oh, what the heck. I just installed SQL Server 2008 on my MacBook Pro, maybe I'll install 2000 on it tonight and play with system object triggers....


I thought YOU were the one who was going to live dangerously! ;) - - I have not been bold enough to test this!

Yeah, yeah, yeah. :)

OK, I spent half an hour in SS2000 trying to make a trigger on syscolumns with no success, created the database (CrashTestDummy) for that purpose. The system comes back with "CREATE TRIGGER permission denied on object 'syscolumns', database 'CrashTestDummy', owner 'dbo'." I had reconfigured to Allow Updates to system tables. To continue the experiment, I created a view (_syscolumns) to point to syscolumns, and the trigger came back with Invalid object name 'dbo._syscolumns'.

Creating a view on syscolumns was not a problem.

So 2000 apparently doesn't like you adding triggers to system tables (no big surprise there!), which would seem to indicate that DDL triggers in 2000 are a non-starter.

I'll try it in 2005 and let you know what happens.
Post #582203
Posted Tuesday, October 7, 2008 3:08 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 29, 2014 7:01 AM
Points: 1,571, Visits: 1,852
Wayne,
Wow, good job investigating system tables/views and triggers! (although I did not expect you to drop everything to do it - - I was just reply to your question.)
Your testing is much appreciated.
Post #582213
Posted Tuesday, October 7, 2008 3:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 3, 2012 4:24 PM
Points: 1, Visits: 22
Hi!,
When I try delete a user on database, appears the next error message:

"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. (.Net SqlClient Data Provider)"

The Trigger is the reason for this message?

tks.
Post #582228
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse