June 11, 2010 at 3:25 am
hi,
I want to create a triger that will run all the time after after a new database that was created.
it's is posibile ? how should I begin ?
June 11, 2010 at 3:32 am
This was removed by the editor as SPAM
June 11, 2010 at 5:36 am
while there is an event for CREATE_DATABASE you can use for a server trigger, stewartc's questions are right on track....it depends on what you want to do;
he hinted at auto-creating something when you create a new database, which is actually easier to just put in the model database and avoid the trigger altogether,but if you want a solid answer, give us the specific details of what you want to do.
in the meantime, you can look at BOL for DDL triggers:
CREATE TRIGGER [ddl_newdb]
ON ALL SERVER
FOR CREATE_DATABASE, ALTER_DATABASE
AS
BEGIN
...
END
Lowell
June 12, 2010 at 12:19 pm
hi, here is all the details the type of the triggrt is database, that should run whenever anyone create a new database.
the trigger need to backup the new database that was created.
hope that you can help me ?
thank's.
June 12, 2010 at 1:33 pm
why back it up? at the moment of creation it is an empty copy of model. it would be better to simply schedule a script to backup every db at the end of the day instead.
anyway, you can't backup the db till after it is created, so I don't think you can do it in a ddl trigger; you could creat a job that would back it up, but not a direct command.
Lowell
June 12, 2010 at 3:12 pm
hi,
I mean somthing like that :
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
declare @db nvarchar(200)
declare @STR
set @STR='backup database'+@db+'to disk = d:\backup\'+@db+'.bak'
exec @STR
END
GO
@db is varible that need somehow to get the name of the database that created.
?
June 13, 2010 at 5:28 am
Lowell (6/12/2010)
why back it up? at the moment of creation it is an empty copy of model.
One motivation might be to take the log out of auto-truncate mode (assuming model is not using the simple recovery model).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 7, 2010 at 4:01 am
CREATE TRIGGER [TDE_TR_LS]
ON ALL SERVER
AFTER CREATE_DATABASE
AS
--Get the Name of the DB and store it in @DBname
DECLARE @data XML
DECLARE @DBname nvarchar(max)
set @data = EVENTDATA()
set @DBname=@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')
--ENDE Get the Name of the DB and store it in @DBname
DECLARE @Backup nvarchar(max)
set @Backup='BACKUP DATABASE '+@DBname+' TO DISK ="c:\backup\'+@DBname+'.bak";'
hope this helps
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply