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?
|
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.
https://technet.microsoft.com/en-us/library/ms190989(v=sql.105).aspx
No comments:
Post a Comment