LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » Stored Procedures, Triggers, & Functions » help with this trigger for create_database,drop_database

help with this trigger for create_database,drop_database

Options

Previous Topic · Next Topic

avipenina

Posted: Sunday, March 30, 2008 6:06:25 AM

Rank: Newbie

Joined: 8/7/2007
Posts: 4
Points: -85

Hi'

i want to write this trigger and a don't know how.i need you help Plz.

i want to be notify by email when a new database is created or delete and the name of the database.i try to write the script but i don't know how to write the if statement and to set the dbname

create trigger alert_on_db_create_delete on all server for Create_Database,Drop_Database
as
if (Create_Database)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile',
@recipients = 'email',
@subject = 'a database was created' + dbname;
else
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile',
@recipients = 'email',
@subject = 'a database was deleted' + dbname;

Thx

Scott Whigham

Posted: Wednesday, April 02, 2008 10:31:01 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: Dallas, TX

Try this:

Code:

alter trigger alert_on_db_create_delete on all server for Create_Database,Drop_Database
as
IF( EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'sysname') = 'CREATE_DATABASE')
PRINT 'You created a database!'
ELSE
PRINT 'You deleted a database!'
GO

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.