What is a Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be considered to be similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself; so when the trigger is fired because of data modification, it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

Types of Triggers:

There are two types of Triggers.

1)      DML Trigger

There are two types of DML Triggers

1.Instead of Trigger
Instead of Triggers are fired in place of the triggering action such as an insert, update, or delete.

2. After Trigger
After triggers execute following the triggering action, such as an insert, update, or delete.

2)      DDL Trigger

This type of trigger is fired against Drop Table, Create Table, Alter Table or Login events. DDL Triggers are always After Triggers.

How to alter modification in database by system admin himself?

How to prevent accidents due to fat fingers? (Accidental execution of code)

How to display message to contact another developers when another developer tries to modify object another developer working on?

It was interesting conversation. Answer to all the questions is correct assignment of permissions and (when permissions are not an issue) Server and Database Level DDL Triggers. If developers have their own login to SQL Server and it does not have permissions to drop or create objects this will not be issue at all. However, there are still risk of System Admin himself making accidental mistakes. The solution to this will be use Server and Database Level DDL Triggers.

DDL is abbreviation of Data Definition Level. DDL contains schema of the database object. It was always dream of all DBA, when change in mission critical schema of the database or server is attempted it is prevented immediately informing DBA and users automatically. DDL Trigger can now make this dream true. Definition of DDL Trigger (from BOL) is 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 in the database such as auditing and regulating database operations.

DML is abbreviation of Data Manipulation Level. DML contains the actual data which is stored in the database schema. UPDATE, INSERT, DELETE, SELECT are clause are used to manipulate database. There is following different between DML and DDL triggers.

• DDL triggers do not support INSTEAD OF feature DML triggers.
• DDL triggers do not have feature of inserted and deleted tables like DML triggers as it does not participate in database manipulations.

Following example demonstrates how DDL trigger can be used to prevent dropping stored procedure.

Step 1 :

First create any sample stored procedure.
USE AdventureWorks;
GO
CREATE PROCEDURE TestSP            
AS
SELECT 1 test;
GO 

Step 2 :

Create DDL trigger which will prevent dropping the stored procedure.

USE AdventureWorks
GO 
CREATE TRIGGER PreventDropSP             
ON DATABASE            
FOR DROP_PROCEDURE            
AS
PRINT 'Dropping Procedure is not allowed. DDL Trigger is preventing this from happening. To drop 

stored procedure run following script.Script : DISABLE TRIGGER PreventDropSP ON DATABASE; ; 

ENABLE TRIGGER PreventDropSP ON DATABASE;'
ROLLBACK;
GO 

Step 3 :

Now test above trigger by attempting to drop the stored procedure.

USE AdventureWorks
GO
DROP PROCEDURE TestSP;
GO 

This should throw following message along with error code 3609 :

Dropping Procedure is not allowed.

DDL Trigger is preventing this from happening.

To drop stored procedure run following script.

Script :

DISABLE TRIGGER PreventDropSP ON DATABASE;

;

ENABLE TRIGGER PreventDropSP ON DATABASE;

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

Step 4 :

Now DISABLE above trigger and it will let you successfully drop the stored procedure previously attempted to drop. Once it is dropped enable trigger again to prevent future accidents.

USE AdventureWorks
GO            
DISABLE TRIGGER PreventDropSP ON DATABASE;
DROP PROCEDURE TestSP;
ENABLE TRIGGER PreventDropSP ON DATABASE; 
GO 

List of all the DDL events (DROP_PROCEDURE in example above) to use with DDL Trigger are listed on MSDN.