June 27, 2013 at 4:55 am
Can I write a DDL trigger to prevent drop of a particular database? I tried this but didn't work:
CREATE TRIGGER Prevent_DB_Drop
ON ALL SERVER
FOR DROP_DATABASE
AS
DECLARE @oname NVARCHAR(100)
SELECT @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')
IF @oname IN ('testDB')
BEGIN
PRINT 'You are not allowed to drop this Database.'
ROLLBACK;
END
GO
ENABLE TRIGGER [Prevent_DB_Drop] ON ALL SERVER
drop database test
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 27, 2013 at 5:27 am
you had most of it, it looks like, but you forgot to handle the EVENTDATA() function, which ahs the currently scoped event information
CREATE TRIGGER Prevent_DB_Drop
ON ALL SERVER
FOR DROP_DATABASE
AS
DECLARE @oname NVARCHAR(100),
@eventData XML
--set the EVENTDATA() functions value to our xml so we can parse it
SET @eventData = EVENTDATA()
SELECT @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')
IF @oname IN ('testDB')
BEGIN
PRINT 'You are not allowed to drop this Database.'
ROLLBACK;
END
GO
ENABLE TRIGGER [Prevent_DB_Drop] ON ALL SERVER
Lowell
June 27, 2013 at 5:44 am
Interestingly I got this script from one of your script for tables and modified it for database. While pasting here, I missed the part that you added. But after all this , it doesn't work.
I can happily drop the database with sa account without any error or warning.
Lowell (6/27/2013)
you had most of it, it looks like, but you forgot to handle the EVENTDATA() function, which ahs the currently scoped event information
CREATE TRIGGER Prevent_DB_Drop
ON ALL SERVER
FOR DROP_DATABASE
AS
DECLARE @oname NVARCHAR(100),
@eventData XML
--set the EVENTDATA() functions value to our xml so we can parse it
SET @eventData = EVENTDATA()
SELECT @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')
IF @oname IN ('testDB')
BEGIN
PRINT 'You are not allowed to drop this Database.'
ROLLBACK;
END
GO
ENABLE TRIGGER [Prevent_DB_Drop] ON ALL SERVER
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 27, 2013 at 7:13 am
ahh, i see it now;
objectname is fine for objects, like tables/procs(anything that exists in sys.objects), but you need a different field in the xml data for the database name
CREATE TRIGGER Prevent_DB_Drop
ON ALL SERVER
FOR DROP_DATABASE
AS
DECLARE @DatabaseName NVARCHAR(100),
@eventData XML
--set the EVENTDATA() functions value to our xml so we can parse it
SET @eventData = EVENTDATA()
SELECT @DatabaseName = @eventData.value('(/EVENT_INSTANCE/DatabaseName)[1]' ,'varchar(128)' ),
IF @DatabaseName IN ('testDB')
BEGIN
PRINT 'You are not allowed to drop this Database.'
ROLLBACK;
END
GO
ENABLE TRIGGER [Prevent_DB_Drop] ON ALL SERVER
Lowell
June 27, 2013 at 8:35 am
Great Sir. it worked. thanks
Lowell (6/27/2013)
ahh, i see it now;objectname is fine for objects, like tables/procs(anything that exists in sys.objects), but you need a different field in the xml data for the database name
CREATE TRIGGER Prevent_DB_Drop
ON ALL SERVER
FOR DROP_DATABASE
AS
DECLARE @DatabaseName NVARCHAR(100),
@eventData XML
--set the EVENTDATA() functions value to our xml so we can parse it
SET @eventData = EVENTDATA()
SELECT @DatabaseName = @eventData.value('(/EVENT_INSTANCE/DatabaseName)[1]' ,'varchar(128)' ),
IF @DatabaseName IN ('testDB')
BEGIN
PRINT 'You are not allowed to drop this Database.'
ROLLBACK;
END
GO
ENABLE TRIGGER [Prevent_DB_Drop] ON ALL SERVER
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy