ddl trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ddl trigger

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-09-02 : 10:12:38

i added a new ddl trigger to my server,
the problem i dont know how to add my DB Name to server

i write this trigger but i have problem in the @body

CREATE TRIGGER [dll_Audit_Drop_DB]
ON ALL SERVER
FOR DROP_DATABASE
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'sql',
@recipients = 'myEmail@SQL.com',
@body = 'Database' + EVENTDATA().value ('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')+' was deleted/detached from server',@subject = 'Reminder';

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-02 : 10:13:42

use db_name()

Madhivanan

Failing to plan is Planning to fail

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-09-02 : 10:25:19

how can i to concatenate in the @body

i try this

@body = db_name() + 'was Deleted',

OR

DECLARE @eventType sysname;
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
...
...
@body = @eventType + 'the database is deleted',

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 10:35:20

what happened when u tried former?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-02 : 11:31:45

I think the problem is that the SP parameter value assignment can't be an expression. Use a local variable to build body and then pass the local variable as the @body parameter.

Be One with the Optimizer
TG

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-09-02 : 12:21:26

sorry,
i dont understand you.

@body = paramter + sring ---> how can i do it?
@body = eventType + 'is Deleted '

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-02 : 13:21:52

declare @local_body <same datatype as @body>

set @local_body = db_name() + ' was deleted'

exec sp_send_dbmail @body = @local_body

Be One with the Optimizer
TG

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-09-02 : 15:33:07

how can i know what is the data type of @body?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-02 : 15:39:16

One way is to use Books Online:
http://technet.microsoft.com/en-us/library/ms190307.aspx

Be One with the Optimizer
TG

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-09-03 : 05:31:25

thanks is working