December 3, 2012 at 3:11 am
According to BOL, DDL trigger respond to CREATE, ALTER, DROP + other T-SQL commands.
I'm currently using two triggers for when a DB is created/dropped but I want to deal with databases that get renamed.
I have a backup schedule table. When a database is created, a trigger fires which creates 2 years’ worth of entries for this new database in the backupschedule table.
If a database is deleted, another trigger removes the entries from the backupschedule table.
However, I had a problem with one database over the weekend because it got renamed and my backupschedule table didn't get updated.
The trigger that generates the backupschedule looks like this:
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
Is it possible to make one if the database is renamed?
---------------------------------------------------------
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
December 3, 2012 at 4:07 am
Hello,
Renaming a database will include the keyword 'ALTER'.The backup schedule was unaffected by renaming of the database because the triggers you have acts on events of 'CREATE' and 'DROP' and not for 'ALTER' statement.
You need to create another DDL trigger which tracks the 'ALTER DATABASE' conditions and performs the desired actions (Updating the backup schedule in this case).
Let me know if this solves your query.
Regards..
December 3, 2012 at 4:57 am
Thanks arunyadav007, I understand this part but I'm not really sure how to actually implement the ALTER_DATABASE trigger.
When the new trigger fires, how do I access the old database name and the new one? This is the bit that I'm not sure about.
Once I have this info, it's easy enough to update the backupschedule table.
---------------------------------------------------------
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
December 3, 2012 at 5:52 am
This is my new ALTER_DATABASE trigger:
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
.......but this seems to fire even when I create a database. I have a feeling when I create a database using SSMS, SQL Server is executing a series of T-SQL statements which also includes an ALTER statement.
---------------------------------------------------------
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
December 3, 2012 at 6:10 am
arunyadav007 pointed you in the right direction, here's a code model for your trigger.
I',m not sure if you are just inserting the changes, updating existing, or what;
i'd think there needs to be a trigger to delete rows from your main history table for when something gets dropped.
--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
Lowell
December 3, 2012 at 6:30 am
the event data is going to have the new database name...it's already been changed by thetime you get to the ddl trigger, unless you force a rollback.
you could rollback , get the name, and then re-execute the query i guess, but that's a lot of effort for somethign simple.
otherwise you'll have to parse the sql statement to get the previous database name...that's going to be a little harder.
i think you might want to consider saving the db_id() of the database...then it doesn't matter the whether the name changes or not.
select db_id('Sandbox')
select database_name(7)
Lowell
December 3, 2012 at 6:36 am
Lowell (12/3/2012)
the event data is going to have the new database name...it's already been changed by thetime you get to the ddl trigger, unless you force a rollback.you could rollback , get the name, and then re-execute the query i guess, but that's a lot of effort for somethign simple.
otherwise you'll have to parse the sql statement to get the previous database name...that's going to be a little harder.
i think you might want to consider saving the db_id() of the database...then it doesn't matter the whether the name changes or not.
select db_id('Sandbox')
select database_name(7)
I like the idea of using the database ID however, from the above, I already have the code that works it out:
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
Although I'm not sure how the above would work if the database was renamed via the GUI.
I also like your idea of using just one trigger to handle the three event types but the problem is, when I create a database, SQL Server is also firing the ALTER_DATABASE event type. Very strange....
This is how the new trigger looks like:
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
As I said, this isn't working properly because when I create a new database, it fires both the CREATE_DATABASE and ALTER_DATABASE parts of the trigger. This is why I wodnered if in the background SQL Server is running an ALTER DATABASE command after the CREATE DATABASE when one creates it via the SSMS GUI.
---------------------------------------------------------
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
December 3, 2012 at 6:45 am
yeah, i just scripted a CREATE DATABASE from the GUI; you can see it does a bunch of alters for the properties after it does the create:
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
Lowell
December 3, 2012 at 9:37 am
Thanks for the confirmation.
I have managed to resolve the problem with the following trigger which I have tested and seems to work like a charm!
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
---------------------------------------------------------
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
December 3, 2012 at 9:55 am
glad it's working for you!
one more thing to consider:
if you create a new database via a RESTORE, that does not trigger the CREATE_DATABASE event. that triggers the event AUDIT_BACKUP_RESTORE_EVENT, which is not a DDL event (so you cannot modify your trigger to capture that too),
you have to add an extended event AUDIT_BACKUP_RESTORE_EVENT to the server to capture and do the equivalent of what you are doing now under CREATE_DATABASE.
just one more thing to consider.
Lowell
December 3, 2012 at 10:33 am
Lowell (12/3/2012)
glad it's working for you!one more thing to consider:
if you create a new database via a RESTORE, that does not trigger the CREATE_DATABASE event. that triggers the event AUDIT_BACKUP_RESTORE_EVENT, which is not a DDL event (so you cannot modify your trigger to capture that too),
you have to add an extended event AUDIT_BACKUP_RESTORE_EVENT to the server to capture and do the equivalent of what you are doing now under CREATE_DATABASE.
just one more thing to consider.
Ah!! Good thinking.
I'm not familliar with extended events to be honest, so I have some reading to do before I can complete this solution.
I wonder, is this something available in 2005 also as the majority f the instances I'm looking after are 2005 still. 🙁
---------------------------------------------------------
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply