Scripting out a DDL trigger

A DDL trigger helps you to monitor when a table is Created , altered or dropped. You can perform some auditing whenever these happen.

You can create a database scoped DDL trigger as shown below

create trigger ddl_trigger on database
for
drop_table,
alter_table
as
select
'access denied to alter/drop this table'
rollback

But you can not see the script of DDL trigger using SP_HELPTEXT like other DML triggers

When you execute the below

EXEC sp_helptext ddl_trigger

You get the following error


Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54
The object 'ddl_trigger' does not exist in database 'test' or is invalid for this operation.

The other way is to make use of sys.triggers catalog view to identify and script out the definition using Object_definition function

select object_definition(object_id) as definition from sys.triggers
where parent_class=0

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s