Trigger for new Database

I got a mail asking for trigger on new Database

Hi.
I need to implement a trigger to email me when someone creates a database on a server without letting me know.I dont know how to write the trigger.Can you help please?
Thanks
Rezwan



Ans:-
you have to use ddl trigger which controls different activity on the server

Here is example for trigger on create database statement

CREATE TRIGGER MONITOR_DATABASE
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
DECLARE @CREATE_DB_STATEMENT VARCHAR(MAX)

SELECT @CREATE_DB_STATEMENT =
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

SELECT @CREATE_DB_STATEMENT
/* use sp_send_dbmail statement here */
END

you have to use sp_send_dbmail in trigger to send mail and use
@CREATE_DB_STATEMENT as message for mail .

Cheers
Amish Shah

No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts