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.
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)','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.' 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.