DDL Trigger

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 291
CREATE PROCEDURE TestSP             
AS
SELECT 1 from test_db;
GO

CREATE TRIGGER PreventDropSP
ON DATABASE
FOR DROP_PROCEDURE
AS
PRINT 'Dropping Procedure is not allowed. DDL Trigger is preventing this from happening. To drop stored procedure run following script.
Script : DISABLE TRIGGER PreventDropSP ON DATABASE; <Run your DROP SP>; ENABLE TRIGGER PreventDropSP ON DATABASE;'
ROLLBACK;
GO
DROP PROCEDURE TestSP;
GO
/*Dropping Procedure is not allowed. DDL Trigger is preventing this from happening. To drop stored procedure run following script.
Script : DISABLE TRIGGER PreventDropSP ON DATABASE; <Run your DROP SP>; ENABLE TRIGGER PreventDropSP ON DATABASE;
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.*/

Now DISABLE above trigger and it will let you successfully drop the stored procedure previously attempted to drop. Once it is dropped enable trigger again to prevent future accidents.

DISABLE TRIGGER PreventDropSP ON DATABASE;
DROP PROCEDURE TestSP;
ENABLE TRIGGER PreventDropSP ON DATABASE;
GO

Comments or Responses

Login to post response