Hi Friends,

In this post I am going to brief you about DDL Triggers and how you can utilize them to display your own custom messages instead of “Command(s) completed successfully.”

DDL Triggers are special triggers which fire in response to Data Definition Language statements. They are used for administrative tasks in database such as auditing and regulating database operations.

They can be created on database level and on server level. Unlike DML triggers, no virtual tables (inserted and deleted) are created when they are fired.

You can specify individual events or event groups in trigger on which you want them to fire.

http://technet.microsoft.com/en-us/library/bb510452(v=sql.100).aspx

The Information about an event that fires a DDL trigger is captured by using a function, EVENTDATA which returns an XML value.  By default, the schema definition for all events is installed in the following directory:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd

Copy and paste the below trigger in SSMS and execute it.

 

The above trigger fires on events related to tables, views, functions, procedures and triggers. You can specify events or event groups according to your requirement. After creating the above trigger, try creating objects whose related events are mentioned in the trigger.

1_SQL_Server_Display_Custom_message_Instead_Command_completed_successfully.

Happy Learning!!!

 

Regards

Mridul Chandhok

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook