anujkumar.mca - Wednesday, January 3, 2018 7:10 AM

Hey guys,

I need to create a trigger which can prevent to drop an online database. Trigger is working with normal condition but I am not able to apply database state condition (ONLINE). I am using below code but not able to fetch state_desc. Please help !
Alter TRIGGER StopDBDrop
ON ALL SERVER
FOR DROP_DATABASE
AS
DECLARE @DatabaseName NVARCHAR(100),
        @DBState NVARCHAR(100),   @SQL nvarchar(800)

--set the EVENTDATA() functions value to our xml so we can parse it
   
SELECT @DatabaseName  = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(128)' )
select @DBState= state_desc from master.sys.databases where name=@DatabaseName

--if(@DBState!='OFFLINE')
--BEGIN
  RAISERROR('Only Offline database can be dropped. For more clarification please contact SQL Run team', 16,1);
  ROLLBACK;
--END
GO