SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Monitoring Changes in Your Database Using DDL Triggers


Monitoring Changes in Your Database Using DDL Triggers

Author
Message
Wayne West
Wayne West
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5784 Visits: 3702
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?

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Carla Wilson-484785
Carla Wilson-484785
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2288 Visits: 1951
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.
Wayne West
Wayne West
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5784 Visits: 3702
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.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Pieter-423357
Pieter-423357
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 Visits: 577
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.

Carla Wilson-484785
Carla Wilson-484785
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2288 Visits: 1951
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.... Hehe


I thought YOU were the one who was going to live dangerously! Wink - - I have not been bold enough to test this!
Pieter-423357
Pieter-423357
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 Visits: 577
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
Wayne West
Wayne West
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5784 Visits: 3702
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.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Wayne West
Wayne West
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5784 Visits: 3702
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.... Hehe


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

Yeah, yeah, yeah. Smile

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.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Carla Wilson-484785
Carla Wilson-484785
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2288 Visits: 1951
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.
Giovanny Gomez Convers
Giovanny Gomez Convers
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search