Monday, August 3, 2015

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 on SQL - DML Triggers.

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

No comments:

Post a Comment

Popular Posts