SQL - DDL Triggers

They come into the lime light to answer the some basic questions which are very important factors for your database such as who is changing your business objects (tables, views, stored procedures, functions etc.) or creating new one, or who actually deleted one or more objects or creating new once means who is the black sheep?

DDL triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks (auditing and regulating database operations) in the database. A DDL trigger can be created on the database level or on the server level. DDL triggers are similar to regular triggers which fire in response to a variety of Data Definition Language (DDL) events.

These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP and certain system stored procedures that perform DDL-like operations can also fire DDL triggers.

The scope of the DDL trigger depends on the event. Simply, we can say that they fire only after the DDL statements execute.

Ideal Condition to Use DDL Triggers

We know that they are very special kind of the triggers and will be used in some ideal condition such as auditing and regulating the database operations such as –

• Prevent certain changes to your database schema - This is the main objective of the DDL triggers where you want to capture every action which is related to database schema and need an action alert and save the changes if occurred.

Different between DML and DDL triggers

DDL triggers and DML triggers are used for different purposes and they have the following difference-

• DML triggers operate on INSERT, UPDATE, and DELETE statements, and help to enforce business rules and extend data integrity when data is modified in tables or views.
• DDL triggers operate on CREATE, ALTER, DROP, and other DDL statements and stored procedures that perform DDL-like operations. They are used to perform administrative tasks and enforce business rules that affect databases. They apply to all commands of a single type across a database, or across a server.
• DML triggers and DDL triggers are created, modified, and dropped by using similar Transact-SQL syntax, and share other similar behavior.
• Like DML triggers, DDL triggers can run managed code packaged in an assembly that was created in the Microsoft .NET Framework and uploaded in SQL Server. For more information.
• Like DML triggers, more than one DDL trigger can be created on the same Transact-SQL statement. Also, a DDL trigger and the statement that fires it are run within the same transaction. This transaction can be rolled back from within the trigger. Serious errors can cause a whole transaction to be automatically rolled back. DDL triggers that are run from a batch and explicitly include the ROLLBACK TRANSACTION statement will cancel the whole batch. 
• Like DML triggers, DDL triggers can be nested. 
• DDL triggers run only after a Transact-SQL statement is completed. DDL triggers cannot be used as INSTEAD OF triggers.

Understanding the functionality of DDL Triggers

As we understood now that they can fire in response to a Transact-SQL event processed in the current database, or on the current server. The scope of the trigger depends on the Transact-SQL events.

USE AdventureWorks2012 Go -- Create DDL Trigger CREATE TRIGGER PreventSecurity ON Database -- Preventing drop tablep For DROP_TABLE, -- Preventing alter table ALTER_TABLE, -- Preventing drop stored procedures DROP_PROCEDURE  AS BEGIN -- print the alert message whenever meet the event PRINT 'Dropping business objects (tables, views and stored procedures) is not permitted. DDL Trigger is preventing this from happening and you must disable Trigger "safety" to drop or alter tables!' -- roll back the action ROLLBACK; END

After creating the preventing DDL trigger to our database, then we need to test the functionality of it by using the run the drop command into the any one existing data table as shown below-

USE [AdventureWorks2012] GO /****** Object:  Table [dbo].[SalesSummary]    Script Date: 08/03/2015 17:56:21 ******/ IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SalesSummary]') AND type in (N'U')) DROP TABLE [dbo].[SalesSummary] GO

You can see that there will be a message display in the message window which comes from the DDL trigger as defined above.

Now, we need to try to alter this table to check the prevent security which is defined in the above DDL trigger as shown below-

USE [AdventureWorks2012] GO /****** Object:  Table [dbo].[SalesSummary]    Script Date: 08/03/2015 17:56:21 ******/ ALTER Table [SalesSummary] add modifiedDate datetime default(getdate()) GO

You can see that the same message is displayed in the message window which comes from the DDL trigger as defined above.

Now, we need to try to drop the existing stored procedure to check the prevent security which is defined in the above DDL trigger as shown below-

USE [AdventureWorks2012] GO /****** Object:  StoredProcedure [dbo].[sp_GenerateTable]    Script Date: 08/03/2015 18:06:21 ******/ IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GenerateTable]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[sp_GenerateTable] GO

You can see that the same message is displayed in the message window which comes from the DDL trigger as defined above.

How to Disable DDL Trigger

Whenever we create a DDL Trigger, it is enabled and will start working immediately. To disable it, we can run the following script as shown below:

USE AdventureWorks2012 Go -- disable DDL Trigger DISABLE TRIGGER PreventSecurity ON Database; Go

How to Enable DDL Trigger

To enable it, we can run the following script as shown below:

USE AdventureWorks2012 Go -- enable DDL Trigger ENABLE TRIGGER PreventSecurity ON Database; Go

DDL Triggers will not be transparent to users - first of all, they can see them in the Object Explorer tree, so it won't be a big secret that they are there and operational. They also appear in execution plans; if users have this option enabled when they create or modify objects in Management Studio, they will see the query plan for statements. 

Database-scoped DDL triggers are stored as objects in the database in which they are created. DDL triggers can be created in the master database and behave just like those created in user-designed databases. You can obtain information about DDL triggers by querying the sys.triggers catalog view. You can query sys.triggers within the database context in which the triggers are created or by specifying the database name as an identifier, such as master.sys.triggers.

Click on Triggers to know more.

Click to know more about the difference between Triggers and Stored Procedures.

Click on Benefits of Triggers.

Click on Disadvantages of Triggers.

Click on SQL - DML Triggers.

References:

https://technet.microsoft.com/en-us/library/ms190989(v=sql.105).aspx

SQL – Triggers

Triggers are extremely powerful and useful feature of SQL because they are completely Global and Stealthy. They are good and necessary for data integrity as well as complete the DBMS-specific activity under the control of the database where it belongs. They are basically used for audit logging.

“Triggers are stored programs, which are automatically executed or fired when some events occur on the table like insertion, deletion or updation of data. It is a database object which could be defined on the table, view, schema, or database with which the event is associated”.

Benefits of Triggers

There is any number of scenarios where a trigger is the best solution to a problem where you are not in immediate control of both the code and the database by applying a patch to your database immediately then you can put a trigger on a table to perform some additional functionality.

Triggers can be written for the following purposes:

• Generating some derived column values automatically
• Enforcing referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• Preventing invalid transactions

As we know that triggers are necessary to good database design and they play a precious role during refactoring of database sachems, while renaming a column, or splitting a column into two columns or vice-versa and assisting the transition.

Disadvantages of Triggers

Triggers have a valid use but far too often as a retro-hack that ultimately makes things worse such as-

1. Trigger execution cannot be seen by the client application.
2. Trigger logic can be difficult to track.
3. The inheritors of your code might not realize triggers have been implemented.
4. Triggers are known for inhibiting performance because they have a small overhead over having the logic in the stored procedure.
5. Triggers work over tempdb, so extensive use of them needs to be taken into account when thinking about tempdb.
6. Sometimes it takes time to remember you have a trigger when something weird happens after data is updated / inserted to the table.

Type of Triggers

SQL have the following types of triggers as given below:

• DML Triggers - A DML trigger is an action programmed to execute when a Data manipulation language (DML) event occurs in the database server. DML events include UPDATE, INSERT, or DELETE statements issued against a table or view.

1. A) Instead of Trigger: Instead of trigger are fired in place of the triggering action such as an insert, update, or delete
2. B) AfterTrigger: After trigger execute following the triggering action, such as an insert, update, or delete.

• CLR Triggers - CLR triggers are special type of triggers that based on the CLR (Common Language Runtime) in .net framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of .NET languages like C#, Visual Basic and F#.

• DDLTrigger – A DDL Trigger is a special kind of trigger that fires in response to Data Definition Language (DDL) statements like Drop Table, Create Table, Or Alter Table. They can be used to perform administrative tasks in the database such as auditing and regulating database operations.

• Logon trigger - This type of trigger is fired against a LOGON event before a user session is established to the SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established.

Difference between Trigger and Stored Procedures

Contents	Triggers	Stored Procedures
Implementation	Triggers can only be implemented on tables or views. A trigger cannot be written within a stored procedure.	Stored Procedure in independent code that can be specific to database. A stored procedure can be written within a trigger
Event and action	Triggers are event and action based to perform some specific tasks and they are free to run automatically whenever the event is occurred on the table on which the trigger is defined	They are not based on the event and action. They are not able to run automatically and depend on the other manual process.
Execution Mode	They execute implicitly.	A stored procedure must be called explicitly.
Schedule Mode	This is not possible for triggers because they are event and action based.	Stored procedures can be easily scheduled through a job to execute on a predefined time.
Transactions	Transactions are not allowed in Triggers.	Transaction statements like begin transaction, commit transaction, and rollback can be used easily inside a stored procedure.
Parameters	This feature is not applicable in triggers.	Stored Procedures may or may not have one or more input/ output parameters to perform some tasks.
Front end applications	This feature is not applicable for triggers.	Stored Procedures can be easily called from the front end (.asp files, .aspx files, .ascx files, .rdl etc.)
Return Value specific	A trigger cannot return a value.	Stored procedures can return values.
Nesting	Directly you cannot call another trigger within a trigger.	A stored procedure can be called from inside another stored procedure.
Task Specific	The Triggers for auditing work: Triggers normally are used for auditing work. They can be used to trace the activities of table events.	Stored procedures are used for performing tasks. Stored procedures are normally used for performing user specified tasks.

Now, we will learn and understand the functionality of each trigger and how they play in SQL. 
Click on DML Triggers to know more.
Click on Instead of Trigger to know more.
Click on After Trigger to know more.
Click on DDL Triggers to know more.
Click on Logon Triggers to know more.

Learn more on another features of SQL as:

1. SQL - Stored Procedure
2. SQL - Create Stored Procedure
3. SQL - Execute Stored Procedure
4. SQL - Alter Stored Procedure
5. SQL - Delete Stored Procedure
6. SQL - Views
7. SQL - Stored Procedure Vs Ad-Hoc (In-Line)
8. SQL - Stored Procedure Vs Functions
9. SQL - Merge Statement
10. SQL - Functions
11. SQL - Cursors