Problems with creating DDL trigger

• Abu Dina

  SSChampion

  Points: 14155

  More actions

  February 25, 2014 at 4:35 am

  #304768

  I have the following DDL trigger which is suppose to run when a database is renamed or deleted from a SQL instance:

  ALTER TRIGGER [DDLTriggerCreateRenameOrDropDatabaseBackupSchedule]

  ON ALL SERVER

  FOR ALTER_DATABASE, DROP_DATABASE

  AS

  SET NOCOUNT ON

  SET ARITHABORT ON

  SET QUOTED_IDENTIFIER ON

  SET ANSI_NULLS ON

  DECLARE @data xml

  DECLARE @DBName varchar(256)

  DECLARE @EventType varchar(128)

  DECLARE @NewDBName varchar(256)

  SET @data = eventdata()

  SET @DBName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')

  --SET @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)')

  -- IF @EventType = 'ALTER_DATABASE'

  --BEGIN

  --SET @NewDBName = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(256)')

  --IF @NewDBName like '%MODIFY NAME%'

  --BEGIN

  --SET @NewDBName = replace(replace(ltrim(rtrim(reverse(substring(reverse(@NewDBName), 1, charindex('=', reverse(@NewDBName))-1)))), '[', ''), ']', '')

  --UPDATE Database_Backup_Catalogue

  --SET Database_Name = @NewDBName

  --WHERE Database_Name = ltrim(rtrim(@DBName)) -- original DB name

  -- UPDATE Databases_Backup_Log

  --SET Database_Name = @NewDBName

  --WHERE Database_Name = ltrim(rtrim(@DBName)) -- original DB name

  -- UPDATE Databases_Backup_History

  --SET Database_Name = @NewDBName

  --WHERE Database_Name = ltrim(rtrim(@DBName)) -- original DB name

  --END

  --END

  --ELSE IF @EventType = 'DROP_DATABASE'

  --BEGIN

  -- DELETE Databases_Backup_Log

  --WHERE Database_Name = @DBName

  -- DELETE Database_Backup_Catalogue

  --WHERE Database_Name = @DBName

  --DELETE Databases_Backup_History

  --WHERE Database_Name = @DBName

  --END

  --ELSE

  --BEGIN

  --SELECT 0 -- Do nothing!

  --END

  SET NOCOUNT OFF

  SET ARITHABORT OFF

  GO

  ENABLE TRIGGER [DDLTriggerCreateRenameOrDropDatabaseBackupSchedule] ON ALL SERVER

  GO

  However, when I attempt to rename a database I'm getting the following error (which I never use to get before?!)

  

  The problem appears to be with this line:

  SET @DBName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')

  Any ideas?

  ---------------------------------------------------------

  It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
  David Edwards - Media lens[/url]

  Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
  Howard Zinn

• Abu Dina

  SSChampion

  Points: 14155

  More actions

  February 25, 2014 at 4:58 am

  #1691814

  Problem solved.

  I ran the below then created my trigger:

  SET QUOTED_IDENTIFIER ON

  SET ANSI_NULLS ON

  ---------------------------------------------------------

  It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
  David Edwards - Media lens[/url]

  Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
  Howard Zinn