SQL Server – Display Custom message Instead of “Command(s) completed successfully.”

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.

USE AdventureWorks
GO
 
CREATE Trigger CustomMessagesForObjects
ON Database
For DDL_TABLE_VIEW_EVENTS, DDL_PROCEDURE_EVENTS, DDL_FUNCTION_EVENTS, DDL_TRIGGER_EVENTS
AS
Begin
 
-- Declare variables
 
Declare @Action varchar(50), @objectname varchar(50), @objecttype varchar(50), @EventType varchar(50)
Declare @data xml
Set @data=EVENTDATA()
 
-- capture Event information using EVENTDATA function
 
Select @Action = @data.value('(/EVENT_INSTANCE/EventType)[1]','varchar(255)'),
       @objectname =  @data.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(255)'),
       @objecttype = @data.value('(/EVENT_INSTANCE/ObjectType)[1]','varchar(50)')       
 
Select @EventType = Case when Substring(@Action,1,CharIndex('_',@Action,1)-1)= 'CREATE' Then 'CREATED'
                         when Substring(@Action,1,CharIndex('_',@Action,1)-1)= 'ALTER'  Then 'ALTERED'
                         else 'DROPPED' end 
 
-- Display custom message
 
 print  @objecttype + ' '''+@objectname+''' '+  @EventType +' Successfully.'     
	       
End
GO

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.

Happy Learning!!!

Regards

Mridul Chandhok

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook