Avatar of Mel Brooks
Mel Brooks

asked on 

Why is DDL command not executing stored procedure

I'm trying to write a DDL trigger that will send an email if someone tries to a drop a database.  I have dropped my sample code below.  The trigger works in that the Rollback prevents the database from being dropped, but the stored procedure does not appear to run at all and so a message isn't sent.  The same store procedure does work on a similar DDL script for DROP TABLE.

USE [master]
GO

/****** Object:  DdlTrigger [ddl_trig_database]    Script Date: 10/29/2017 11:13:57 PM ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

alter TRIGGER [ddl_trig_database]  
ON ALL SERVER  
FOR Drop_DATABASE  
AS  
   DECLARE @eventData XML,
        @otext VARCHAR(MAX),
        @edate DATETIME,
            @Msg varchar(max)

set @edate=GETDATE()
SET @eventData = EVENTDATA()      
select  @otext=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')      

set @msg = ' Delete attempt on database ' + convert(varchar(26),@edate,100) + '  ' + @otext

exec demo.dbo.SendInternetEmail 'RECIPIENT@gmail.com', 'Dropped database', @msg, 'SENDER@gmail.com'
rollback

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [ddl_trig_database] ON ALL SERVER
GO
DatabasesMicrosoft SQL Server* triggers

Avatar of undefined
Last Comment
Mel Brooks
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Mel Brooks
Mel Brooks

ASKER

I had been trying to delete the test database from the management studio window and didn't get any errors.  I ran a Drop Database command from the query window and received an error about ANSI settings.  I removed the lines:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
I then started receiving blank email messages and was able to correct that by changing the @otext line to:

select   @otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(MAX)')
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo