What is a trigger ? what are the types of Triggers ?


Today, I want to discuss in detail what are Triggers ? what are the types of Triggers In which scenario we use the Triggers these are the things i want to discuss in detail with code snippets and with .Net how to work with these triggers ? .

Definition of a Trigger



A Trigger is a block of code that constitutes a set of T-SQL statements that are activated in response to a Certain actions. A trigger can also interpreted as a special kind of Stored Procedure that is executed whenever an action such as data modification , takes place.

It is fired whenever data in the Underlying table is affected by any of the Data Manipulation Language we call them as D M L statements such as (Insert, update or Delete) , In other words except or other than select statement.

What kind of help we get from triggers :

Triggers help in maintaining Consistent , reliable and correct data into tables . They enable the performance of Complex actions and cascade these actions to other dependent tables.

Characteristics of a Trigger

a) Trigger can be executed automatically and not Manually.

b)It can be triggered automatically by SQL-server whenever there is a manipulation done in the table.

c) The most Important thing it cannot return data to the User

Schema for a Trigger in SQL-server



Create Trigger your trigname
on [your Table name]
[With Encryption] --- optional
For [INSERT] [DELETE] [UPDATE]
AS SQL-STATEMENTS



With Encryption will encrypts the entries made in syscomments table, which displays the text of the trigger that has been created.

And one more Important thing you can use Transaction statements like (Commit , Rollback) when we are creating the triggers.


Whenever a trigger fires in response to the Insert , Update , Delete statements two special tables are created they are insert and delete tables.

Final Word :

We are working with Asp.net and we are writing this article in Dotnetspider website so how to call a trigger from asp.net ?, for asp.net Developers this question churns in their mind for this my answer is Using SQL server Common Language Run time we can work with triggers from front-end .

The Microsoft.Sqlserver.Server namespace contains a new set of application programming Interfaces (API'S ) so that manage code can interact with the Microsoft sql-server statement. This i will discuss in the Next Article.


Article by srirama
A Good advice from parent to a Child , Master to a Student , Scholar to an Ignorant is like a doctor prescribed pill it is bitter to take but when they take it will do all good for them --- Bhushan

Follow srirama or read 73 articles authored by srirama

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: