SQL Server Triggers

What is Trigger:

• Triggers allow us to execute a batch of SQL code when an insert, update or delete command is executed against a specific table
• One of these objects that many developers overlook is the trigger. Triggers are "attached" to a table and allow us to setup our database in such a way that whenever a record is added, updated, or deleted from a table, then SQL server will automatically execute a batch of SQL code after that table modification takes place.
• A trigger is an object contained within an SQL Server database that is used to execute a batch of SQL code whenever a specific event occurs. As the name suggests, a trigger is “fired” whenever an INSERT, UPDATE, or DELETE SQL command is executed against a specific table.
• Sample Syntax for Trigger:
• CREATE TRIGGER tr_Source_INSERT ON Source FOR INSERT AS PRINT GETDATE()

Types of Triggers :
There are some added types in SQL Server 2005 for triggering actions:

1) DML Trigger:

1) AFTER Triggers:

• An AFTER trigger is executed only after the triggering SQL statement, including all referential cascade actions and constraint checks associated with the object updated or deleted, is executed successfully.
• The AFTER trigger operation checks for the effects of the triggering statement as well as all referential cascade UPDATE and DELETE actions caused by the triggering statement.
• AFTER is the default, if only the FOR keyword is specified.
• AFTER triggers may be defined only on tables.
• Sample Example:
  CREATE TRIGGER job_delete ON jobs FOR DELETE AS
  DECLARE @job_id INT
  SELECT @job_id = (SELECT job_id FROM DELETED)
  IF EXISTS (SELECT job_id FROM employees WHERE job_id = @job_id)
  BEGIN
  RAISERROR ('you cannot delete a job that is held by an employee', 16, 1)
  ROLLBACK TRAN
  END
  2)INSTEAD OF Triggers:An INSTEAD OF Trigger is used to perform an action instead of the one that caused the trigger to be fired. This sounds like double-talk, so I will explain a little more. Let's say you have an INSTEAD OF INSERT trigger defined on a table and an INSERT is executed. A row is not added to the table, but the code in the trigger is fired. The following shows what this looks like.
  CREATE TRIGGER tr_Orders_INSERT_InsteadOf
  ON Orders
  INSTEAD OF INSERT
  AS
  PRINT 'Updateable Views are Messy'
  Go

2)DDL Triggers:

• DDL triggers, like regular triggers, fire stored procedures in response to an event. However, unlike DML triggers, they do not fire in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they 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. Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.
• DDL triggers can be used for administrative tasks such as auditing and regulating database operations.
• Use DDL triggers when you want to do the following:
• You want to prevent certain changes to your database schema.
• You want something to occur in the database in response to a change in your database schema.
• You want to record changes or events in the database schema.
• DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers.
• The following example shows how a DDL trigger can be used to prevent any table in a database from being modified or dropped.
• Example:
  CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "safety" to drop or alter tables!' ROLLBACK ;

• DDL triggers can fire in response to a Transact-SQL event that is processed in the current database or on the current server.
• The scope of the trigger depends on the event.
• To obtain a DDL trigger example that is available in the AdventureWorks sample database, in Object Explorer in the SQL Server Management Studio, open the Database Triggers folder located in the Programmability folder of the AdventureWorks database. Right-click ddlDatabaseTriggerLog and select Script Database Trigger as. By default, the DDL trigger ddlDatabaseTriggerLog is disabled.
• Scenarios for Using a DDL Trigger:
• You can prevent or log changes to your database. As a DBA or database developer, it may be important to know if and when something in your database has changed. In some cases, you may even want to prevent changes by any user altogether.
• You may have naming conventions in your database. DDL triggers can enforce them by preventing improperly named tables, views, stored procedures, and even table columns.
• You may want to perform other actions in response to a DDL event. You could, for example, create a record in the server's event log or execute CLR code you've installed on your SQL Server
  3) CLR Triggers: