a question about trigger

Question

a question about trigger

Aviad C.

SSChasing Mays

Points: 602
More actions
June 11, 2010 at 3:25 am

#225477

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Aviad C. SSChasing Mays Points: 602 More actions · Answer 2

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.

Lowell SSC Guru Points: 323518 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Aviad C. SSChasing Mays Points: 602 More actions · Answer 4

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.

?

Paul White SSC Guru Points: 150468 More actions · Answer 5

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
All articles available on SQL.kiwi
@SQL_Kiwi

Markus Summer SSC Veteran Points: 200 More actions · Answer 6

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