DDL Trigger

Question

DDL Trigger

RaviShankar1234

SSCommitted

Points: 1647
More actions
December 16, 2010 at 1:32 pm

#232535

we have maintenance plan that is executed as job everynight at 1:30 am. This maintenance plan has update statistics step , checking the integrity of databases in other steps backing up all the databases on the servers in one step.
Recently I created a DDL Trigger on of one databases because the developers are dropping the users and objects and such this makes my life easy to point the mistake that are being done by the developers. I created a newdatabase with a table in it and Im pointing the DDL actions to inserted into the created database table and this working fine and the table has columsn loginName, executed sqlstatement, dateandtime, log# (this is a surrogate key) , databasename, objectname, objecttype and these columns are set to not null values. I see strange thing that is happening here is whenever the job runs I see that maintenance plan fails. when I check the log of the maintenance plan this is the error im getting it
UPDATE STATISTICS [dbo].[xxxtablename]
WITH FULLSCAN
" failed with the following error: "Cannot insert the value NULL into column 'ObjectName', table 'DB.dbo.tablename'; column does not allow nulls. INSERT fails.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
In the above error the the table and the trigger reside on the same database. I understand this error is caused because of null values but I was wondering why the update statistics would cause trigger to fire?
can any one explain why this is happening.
Thanks,
Ravi

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

looks like the insert statement from your ddl audit has a table that doesn't allow nulls for the object name;

i'm thinking that the creation of statistics might not generate a name for the object (sys.objects?) but a name in sys.indexes.

change your audit table to allow nulls for the objectname, and see if it works after that.

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You don't say what DDL events you're looking at, but if the trigger is on ALL_DDL_EVENTS (or similar), update stats will trigger that. Suggest you exclude that event from the trigger (IF <Extract the event type from EventDate()> != 'Update Statistics' ..._

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RaviShankar1234 SSCommitted Points: 1647 More actions · Answer 3

I guess when updating the statistics the statistics will be updated based on the column not on the object.. Im I right Lowell