Introduction of Triggers in SQL Server

In sql sever,we can create four types of triggers.Which are given below:-

1. DML (Data Manipulation Language) Triggers
2. DDL(Data Definition Language ) Triggers
3. Logon Triggers
4. CLR Triggers

 a.) DML(Data Manipulation Language) Triggers:- We can perform DML Triggers on the FollowingDML statements in sql server such as:

• Insert statement
• Update Statement
• Delete statement
• Stored Procedures 

Types of DML Triggers

There are two types of DML Triggers in sql server which are given below:-

1. After Triggers (For/After clause)
2. Instead of Triggers(Instead of Clause)

  1.) After Triggers (For/After clause):- This Triggers basically runs after an insert,delete and update operations on a table. We can perform After Triggers on a table not a View table. We can use  three types of After Triggers in sql server which are are given below:-

• After Insert Trigger
• After Delete Trigger
• After Update Trigger 

You can understand the actual meaning of 'After Clause '.It means it will be fired after execution of any DML or DDL Statements in sql server. Real Example After/For Triggers Here

Note:- We can't use For/After clause on View Tables in sql database.
     2.) Instead of Triggers (Instead of Clause):-
Instead of Triggers are used as an Interceptor.Suppose we define an Instead of Triggers on table or Views for the delete operation. If any conditions\rules written in statements are not satisfied or any error occurs in sql database then this trigger jump to another statements by escaping some statements and displayed some message to the client.
we can further classified this trigger in three types as given below:-

• Instead of Insert Trigger
• Instead of Delete Trigger
• Instead of Update Trigger

Note:-We can use For/After clause with DDL Triggers but we can't use Instead clause with DDL Triggers.

b.) DDL (Data Definition Language ) Triggers 
   DDL Trigger can be used for Observe ,control action and audit operations on the sql server. DDL Trigger are basically used for administration operations such as creating auditing and controlling the database operations. We can create DDL Triggers on the Tables by following statements as given below:- 

• Create Statement
• Alter Statement
• Drop Statement
• Stored procedures can perform on DDL Trigger

c.) Logon Trigger
Logon Triggers are DDL Triggers that is created at server label. Logon triggers are a spacial type of triggers which are fired when a logon event of sql server occurs. Suppose you are not establishing  sessions with sql server then it will show an error which is already defined in sql statements.If any user establish session correctly to the sql server then it will show some other type of message to the client which is already written in sql statements.It is helpful to show the logon activity or create limited session for a specific user .

Syntax of Logon Triggers

create trigger trigger_name
on {table/view}
[with encryption/Execute as]
[{for/after/instead of}/{insert/delete/update/create/alter/drop}]
[Not for replication]
as
SQL statement(1,2,3......n)

Descriptions:-

1. trigger_name:-Here you can  specify your trigger name which you want to create on table/view.
2.  table/view:-Here you will specify your table or view on which the trigger is to be created.
3. with encryption/Execute as:- It is optional statement.it is used for encryption and security purpose on table.
4.  for /after/instead of:- This statement is used in DML Triggers. i will explain this statements in details.
5. insert/delete/update/create/alter/drop:- This keyword specify on which action trigger should be fired. 
6. Not for replication :- Trigger should not be fired if any replication process find on table.
   
7. <AS:- We specify the Actions and conditions after this statement.
   
8. sql statement:- Here we specify the sql statements (actions and conditions etc.).
   

d.) CLR Trigger:-
CLR Trigger are special type of triggers that are based on the CLR (Common Language Run time ) in .NET Framework.we can create DDL and DML Triggers using CLR Language (Visual basic ,C#,F#). CLR Trigger Concepts introduced in SQL Server 2008. 

Where we use Trigger concepts in our real Life:-
Today,Data (Information) is more valuable things for every one.All the world's people want to secure the data (information). Now Days Banks,Google, Facebook ,yahoo and other organisations are securing the data (information) . Triggers concepts are very helpful to secure and backup your data over the web. We can create backup of data using Triggers concept. You can specify the Triggers concepts on Database that are helpful to create a  backup of your database on the server within a fixed time period. Suppose your data is lost due to some reasons ,you can not retrieve it .If you create backup of your data every day then it will be helpful for you. Now days all government organizations used this concepts to secure your valuable data (information) on the server. So Triggers are more helpful in our real life.
   Triggers also helpful to inform if any unauthorized activity logon occurs on a database.    
Note:- I will implement these four Triggers in details in our coming tutorials with real time examples.

For More...

1. Joins in sql server
2. Real Threading concepts in c# 
3. Transactions in sql server
4. Stored procedure concepts in c#
5. How to copy data from one table to another table
6. How to implement identity column in sql server
7. How to create check constraints on column
8. Data integrity in sql server
9. Microsoft sql server
10. Difference between ado and ado.net