CREATE TRIGGER [DDLTriggerCreateDatabaseBackupSchedule] 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
   
   declare @data xml
   declare @DBName varchar(256)
   
   set @data = eventdata()
   set @DBName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
	

 INSERT INTO QbaseDBAUtility.dbo.backupschedule(backupdate, Backupweekday, databasename, BackupType)


 SELECT DATEADD(DD,N,dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))) AS backupdate, 
        DATENAME(DW,DATEADD(DD,N,dateadd(dd, 0, DATEDIFF(dd, 0, getdate())))) AS Backupweekday
        ,b.name
        ,CASE WHEN N % 14 = 0 THEN 'F' ELSE 'D' END AS backuptype
   FROM QbaseDBAUtility.dbo.Tally AS a
   CROSS JOIN sys.databases as b
  WHERE N < 780
  and name = @DBName

CREATE TRIGGER [DDLTriggerRenameDatabaseBackupSchedule] 
ON ALL SERVER 
FOR ALTER_DATABASE 
AS 
   
   DECLARE @data XML
   DECLARE @NewDBName VARCHAR(256)
   DECLARE @PreviousDBName VARCHAR(256)
   
   SET @data = eventdata()
   SET @PreviousDBName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
   SET @NewDBName = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(256)')
   SET @NewDBName = REVERSE(SUBSTRING(REVERSE(@NewDBName), 1, CHARINDEX('=', REVERSE(@NewDBName))-1))
	
	UPDATE QbaseDBAUtility.dbo.BackupSchedule
	SET DatabaseName = @NewDBName
	WHERE DatabaseName = @PreviousDBName

GO

  --see http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd 
  --for the values available for various events
CREATE TRIGGER [DDLTriggerCreateDatabaseBackupSchedule] 
ON ALL Server
--I need the sa context in case i need to access a table that the user, who cna create/alter  database, but does not have access to dbMail or objects used for logging
--i.e. db_owner rights in a given database lets a user alter their own database, but they certainly might not have access to objects used below.
  WITH EXECUTE AS 'sa'
FOR Create_Database,Alter_Database  -- Captures a Create/Alter Database Event
AS
BEGIN --SERVER TRIGGER
DECLARE
    @EventType        varchar(128),
    @PostTime         datetime,
    @SPID             int,
    @ServerName       varchar(128),
    @LoginName        varchar(128),
    @DatabaseName     varchar(128),
    @TSQLCommand      varchar(128),
    @mySubject        varchar(300),
    @data XML
SET @data = EVENTDATA()    
  --load our values to variables in case we need them
  SELECT
    @EventType        = @data.value('(/EVENT_INSTANCE/EventType)[1]'        ,'varchar(128)' ),
    @PostTime         = @data.value('(/EVENT_INSTANCE/PostTime)[1]'         ,'datetime' ),
    @SPID             = @data.value('(/EVENT_INSTANCE/SPID)[1]'             ,'int' ),
    @ServerName       = @data.value('(/EVENT_INSTANCE/ServerName)[1]'       ,'varchar(128)' ),
    @LoginName        = @data.value('(/EVENT_INSTANCE/LoginName)[1]'        ,'varchar(128)' ),
    @DatabaseName     = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]'     ,'varchar(128)' ),
    @TSQLCommand      = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]'      ,'varchar(128)' )

IF @EventType = 'CREATE_DATABASE'
  BEGIN
    INSERT INTO QbaseDBAUtility.dbo.backupschedule(backupdate, Backupweekday, databasename, BackupType)
      SELECT DATEADD(DD,N,dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))) AS backupdate, 
             DATENAME(DW,DATEADD(DD,N,dateadd(dd, 0, DATEDIFF(dd, 0, getdate())))) AS Backupweekday
            ,b.name
            ,CASE WHEN N % 14 = 0 THEN 'F' ELSE 'D' END AS backuptype
      FROM QbaseDBAUtility.dbo.Tally AS a
      CROSS JOIN sys.databases as b
      WHERE N < 780
      and name = @DatabaseName
    END
IF @EventType = 'ALTER_DATABASE'
  BEGIN
    PRINT 'This is Where we isnert or update to teh special table'
  END
END --SERVER TRIGGER  

select db_id('Sandbox')
select database_name(7)

select db_id('Sandbox')
select database_name(7)

SET @data = eventdata()
   SET @PreviousDBName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
   SET @NewDBName = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(256)')
   SET @NewDBName = REVERSE(SUBSTRING(REVERSE(@NewDBName), 1, CHARINDEX('=', REVERSE(@NewDBName))-1))
	
	UPDATE QbaseDBAUtility.dbo.BackupSchedule
	SET DatabaseName = @NewDBName
	WHERE DatabaseName = @PreviousDBName

CREATE TRIGGER [DDLTriggerCreateAlterOrDropDatabaseBackupSchedule] 
ON ALL SERVER 
FOR CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
AS 
   
	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 = 'CREATE_DATABASE'
		BEGIN
			INSERT INTO QbaseDBAUtility.dbo.backupschedule(backupdate, Backupweekday, databasename, BackupType)
			SELECT DATEADD(DD,N,dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))) AS backupdate, 
				DATENAME(DW,DATEADD(DD,N,dateadd(dd, 0, DATEDIFF(dd, 0, getdate())))) AS Backupweekday
				,b.name, CASE WHEN N % 14 = 0 THEN 'F' ELSE 'D' END AS backuptype
			FROM QbaseDBAUtility.dbo.Tally AS a
			CROSS JOIN sys.databases as b
			WHERE N < 780
			and name = @DBName
		END
		
	ELSE IF @EventType = 'ALTER_DATABASE'
		BEGIN
			SET @NewDBName = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(256)')
			SET @NewDBName = reverse(substring(reverse(@NewDBName), 1, charindex('=', reverse(@NewDBName))-1))
	
			UPDATE QbaseDBAUtility.dbo.BackupSchedule
			SET DatabaseName = @NewDBName
			WHERE DatabaseName = @DBName -- original DB name
		END
	
	ELSE 
		BEGIN
			DELETE QbaseDBAUtility.dbo.backupschedule
			WHERE databaseName = @DBName
		END

GO

CREATE DATABASE [Example] ON  PRIMARY 
( NAME = N'Example', FILENAME = N'F:\SQLData\DEV223\Example.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Example_log', FILENAME = N'F:\SQLData\DEV223\Example_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Example] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [Example] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [Example] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [Example] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [Example] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [Example] SET ARITHABORT OFF 
GO
ALTER DATABASE [Example] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [Example] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [Example] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [Example] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [Example] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [Example] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [Example] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [Example] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [Example] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [Example] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [Example] SET  DISABLE_BROKER 
GO
ALTER DATABASE [Example] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [Example] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [Example] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [Example] SET  READ_WRITE 
GO
ALTER DATABASE [Example] SET RECOVERY FULL 
GO
ALTER DATABASE [Example] SET  MULTI_USER 
GO
ALTER DATABASE [Example] SET PAGE_VERIFY CHECKSUM  
GO
USE [Example]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [Example] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

CREATE TRIGGER [DDLTriggerCreateAlterOrDropDatabaseBackupSchedule] 
ON ALL SERVER 
FOR CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
AS 
   SET NOCOUNT 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 = 'CREATE_DATABASE'
		BEGIN
			INSERT INTO QbaseDBAUtility.dbo.backupschedule(backupdate, Backupweekday, databasename, BackupType)
			SELECT DATEADD(DD,N,dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))) AS backupdate, 
				DATENAME(DW,DATEADD(DD,N,dateadd(dd, 0, DATEDIFF(dd, 0, getdate())))) AS Backupweekday
				,ltrim(rtrim(b.name)), CASE WHEN N % 14 = 0 THEN 'F' ELSE 'D' END AS backuptype
			FROM QbaseDBAUtility.dbo.Tally AS a
			CROSS JOIN sys.databases as b
			WHERE N < 780
			and name = @DBName
		END
		
	ELSE 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 QbaseDBAUtility.dbo.BackupSchedule
					SET DatabaseName = @NewDBName
					WHERE DatabaseName = ltrim(rtrim(@DBName)) -- original DB name
				END
		END
	
	ELSE IF @EventType = 'DROP_DATABASE'
		BEGIN
			DELETE QbaseDBAUtility.dbo.backupschedule
			WHERE databaseName = @DBName
		END
	ELSE
		BEGIN
			SELECT 0
		END

SET NOCOUNT OFF