ddl trigger for drop_index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

ddl trigger for drop_index

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2011-03-14 : 08:12:05

Does anyone have an example of a DDL trigger to stop the dropping of primary key? I tried this:

CREATE TRIGGER safety
ON DATABASE
FOR DROP_INDEX
AS
PRINT 'Indexes Can Not be dropped!'
ROLLBACK
;

This works when I test dropping index either through script or locating index in Indexes folder and deleting, but if I use GUI to remove primary key from column, the index gets dropped. How can I stop this?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-14 : 08:18:10

Well...a primary key is defined as a constraint, not an index even though it is listed among the indexes. Look at the drop statement that is generated by SSMS when you try to drop a primary key index:

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[navn]') AND name = N'PK_navn')
ALTER TABLE [dbo].[navn] DROP CONSTRAINT [PK_navn]
GO

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

lappin
Posting Yak Master

182 Posts

Posted - 2011-03-14 : 10:20:58

can I create a DDL trigger on constraint drop?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-03-14 : 11:07:09

Create database trigger for ALTER_TABLE
http://msdn.microsoft.com/en-us/library/ms186406.aspx
http://connectsql.blogspot.com/2009/07/create-ddl-changes-log.html

--------------------------
http://connectsql.blogspot.com/

lappin
Posting Yak Master

182 Posts

Posted - 2011-03-15 : 07:26:20

I looked at the link you sent - thank you. It seems strange that MS haven't given a DROP_Constraint event. Stopping an Alter Table is too extreme for my purposes, thanks anyway. I've set up DDL Logging table - cheers.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-16 : 21:16:59

*** VERY IMPORTANT ***

DDL Triggers are AFTER TRIGGERS. So the index would indeed get dropped, then the drop rolled back.

This can huge performance implications, especially if the index being dropped is the clustered index and there are several non-clustered indexes on the table too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources