February 21, 2010 at 2:05 pm
Hi,
Just was wondering how can I catch a "create index" event with this function? I intend to cancel the command with a message saying: "indexes can not be created in this schema".
Thanks.
February 21, 2010 at 2:08 pm
I haven't checked it out specifically, but I believe that you would want a DDL trigger for CreateIndex in your database.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2010 at 2:27 pm
RBarryYoung (2/21/2010)
I haven't checked it out specifically, but I believe that you would want a DDL trigger for CreateIndex in your database.
Yep, you would have a DDL trigger on your database and use EventData() function to filter out a "create index" event. I could not find a good description for all events for this function. And the piece I am missing is: how do I filter out a "create index" event with this function?
I guess the tricky part of it is: I only want to rollback "create index" commands if they are applied to a specific schema.
😎
February 21, 2010 at 2:48 pm
Roust this seems to be working on my sandbox; i was able to create a table with a pk and a uq, and it iterrupts the create index statement:
CREATE TRIGGER [PREVENTINDEX_EventData]
on DATABASE
FOR
CREATE_INDEX,ALTER_INDEX,DROP_INDEX
AS
begin
RAISERROR ('indexes can not be created in this schema.', -- Message text.
16, -- Severity.
1 -- State.
);
rollback
end
GO
ENABLE TRIGGER [PREVENTINDEX_EventData] ON DATABASE
GO
create table test(testid int identity(1,1) primary key,
testname varchar(30) unique,
texttext varchar(30) )
create index ix_1 on test(texttext)
Msg 50000, Level 16, State 1, Procedure PREVENTINDEX_EventData, Line 8
indexes can not be created in this schema.
Msg 3609, Level 16, State 2, Line 3
The transaction ended in the trigger. The batch has been aborted
Lowell
February 21, 2010 at 2:50 pm
OK, now I have checked it out specifically. Here is the example that I came up with:
CREATE TRIGGER CreateIndex_Prevent
ON DATABASE
FOR CREATE_INDEX
AS
-- Get the EventData XML:
DECLARE @x AS XML
SELECT @x = EVENTDATA()
-- Check DB & Schema:
IF @x.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)') = 'YourDB'
BEGIN
IF @x.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)') = 'YourSchema'
BEGIN
RAISERROR ('DB Trigger "CreateIndex_Prevent" does not allow Indexes to be created in this schema.', 16, 1)
ROLLBACK
END
END
GO
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2010 at 2:52 pm
Lowell (2/21/2010)
Roust this seems to be working on my sandbox; i was able to create a table with a pk and a uq, and it interrupts the create index statement:
I understand this trigger will stop you from creating indexes on any schema within the database?
😎
February 21, 2010 at 3:01 pm
RBarryYoung (2/21/2010)
OK, now I have checked it out specifically. Here is the example that I came up with:
Yep, this one works perfectly. The only thing, I reckon you don't need to check the database name as you create the trigger on that specific database, you would only need to check the schema name.
Where did you find the description for the EventData() function?
February 21, 2010 at 3:44 pm
Roust_m (2/21/2010)
Where did you find the description for the EventData() function?
I didn't, I just opened the XML up and looked at it. Here is an example for CREATE INDEX:
<EVENT_INSTANCE>
<EventType>CREATE_INDEX</EventType>
<PostTime>2010-02-21T16:47:03.623</PostTime>
<SPID>66</SPID>
<ServerName>PPSLT-BYOUNG</ServerName>
<LoginName>PROACTIVEWORLD\byoung</LoginName>
<UserName>dbo</UserName>
<DatabaseName>MyTests</DatabaseName>
<SchemaName>ACPControl</SchemaName>
<ObjectName>IX_JobStat_DUMMY_TEST</ObjectName>
<ObjectType>INDEX</ObjectType>
<TargetObjectName>JobStat_Log</TargetObjectName>
<TargetObjectType>TABLE</TargetObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>CREATE NONCLUSTERED INDEX IX_JobStat_DUMMY_TEST ON ACPControl.JobStat_Log
(
LogID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2010 at 3:48 pm
FYI: The BOL article for EVENTDATA has an cute little script for logging all DDL events in a database. Because I wanted to capture and store the whole original EVENTDATA XML content, I altered it slightly:
CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000), EventXML XML);
GO
CREATE TRIGGER log
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT ddl_log
(PostTime, DB_User, Event, TSQL, EventXML)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
, @data
) ;
GO
--Test the trigger.
CREATE TABLE TestTable (a int)
DROP TABLE TestTable ;
GO
SELECT * FROM ddl_log ;
Put this in one of your sandbox databases, and you should be able to pull up & examine EVENTDATA examples anytime you want to.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply