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('d
set @msg = ' Delete attempt on database ' + convert(varchar(26),@edate
exec demo.dbo.SendInternetEmail
rollback
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [ddl_trig_database] ON ALL SERVER
GO
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('d
set @msg = ' Delete attempt on database ' + convert(varchar(26),@edate
exec demo.dbo.SendInternetEmail
rollback
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [ddl_trig_database] ON ALL SERVER
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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('d