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.
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.
CREATE Trigger CustomMessagesForObjects
For DDL_TABLE_VIEW_EVENTS, DDL_PROCEDURE_EVENTS, DDL_FUNCTION_EVENTS, DDL_TRIGGER_EVENTS
-- Declare variables
Declare @Action varchar(50), @objectname varchar(50), @objecttype varchar(50), @EventType varchar(50)
Declare @data xml
-- capture Event information using EVENTDATA function
Select @Action = @data.value('(/EVENT_INSTANCE/EventType)','varchar(255)'),
@objectname = @data.value('(/EVENT_INSTANCE/ObjectName)','varchar(255)'),
@objecttype = @data.value('(/EVENT_INSTANCE/ObjectType)','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.'
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.