About Triggers in SQL

Triggers
---------

Triggers are the stored sub programs that will automatically invoked based on specified event. Basically these are special type of stored procedure that are automatically fired/executed when a DDL or DML command statement related with the trigger is executed. Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements. 

Types of Triggers

----------------------

Based on Events specified for a trigger, triggers are classified into DDL Triggers and DML Triggers.

 - DDL Triggers are the Triggers that are created by specifying a DDL command as Event.

 - DML Triggers are the Triggers that are created by specifying DML command as Event.

DDL triggers are introduced in SQL Server 2005. In SQL Server we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system defined stored procedures that perform DDL-like operations.

Example : If you are going to execute the CREATE LOGIN statement or the sp_addlogin stored procedure to create login user, then both these can execute/fire a DDL trigger that you can create on CREATE_LOGIN event of Sql Server.
We can use only FOR/AFTER clause in DDL triggers not INSTEAD OF clause means we can make only After Trigger on DDL statements.

DDL trigger can be used to observe and control actions performed on the server, and to audit these operations. DDL triggers can be used to manage administrator tasks such as auditing and regulating database operations.

DML triggers have the following three main purposes:

- Create procedural integrity constraints.

- Record Auditing information of the table.

- Allow insert,update & delete on complex views.

DML triggers are of two types:

1. After Triggers: 

- This Trigger fires after triggering action.  The INSERT,UPDATE,DELETE statements, causes after Inserting,Updating,Deleting Actions.

-  Mainly this are used for Maintaining Inserted, updated, Deleted actions in Audit tables.

Ex:

Table "tblEmployee"

CREATE TRIGGER trig_tblEmployee_Insert/Update/Delete

ON tblEmployee 

FOR INSERT /DELETE

AS 

BEGIN

DECLARE @id int

select @id=Id from inserted // or deleted // inserted and deleted are called Magic Tables, see below for Info,

insert into tblEmployeeAudit(Audit) values("New Employee with Id=cast(@Id as nvarchar(5) is Inserted")// or  Delete query.

END

// A Inserted Information is Added to "tblEmployeeAudit " table in Column "Audit".

Magic Tables:

Related to Triggers there are two table inserted and deleted which were called Magic Tables. This tables are accessible only withiin triggers only. The structure of this tables will be same as currently executing tables within triggers. Main Purpose of this tables is to provide access to new and old values of current row that is INSERTED,UPDATE,DELETED within the Trigger

2. Instead of Triggers.

-  This Triggers will be executed 'Instead of' Executing INSERT,UPDATE,DELETE statements.

-   Main purpose of Instead of Trigger is to allow insert,update,delete on complex views, In Oracle, "Instead of" Triggers can be created only on views, but on SQL Server, "Instead of" Triggers can be on Views as well as Tables.

Example: Create a Trigger on Dept Table on automatically convert Name and Location to Upper Case.

CREATE TRIGGER changecase

on Dept INSTEAD OF  as

BEGIN

Insert Dept select deptId,UPPER(dname),UPPER(location) from inserted

Difference between Triggers and Stored Procedures -

1. Stored procedures must be called by the user only whesreas triggers will be invoked automatically and use cannot invoke manually.
2. Stored procedure can take arguments whereas triggers cannot take arguments because they are automatically invoke.
2. Stored procedure can return a value within ouput parameter. But, trigger cannot return a value either without parameter or return statement