WHAT IS TRIGGER IN SQL SERVER?

 Posted by Sriramnandha on 5/23/2012 | Category: Sql Server Interview questions | Views: 23645 | Points: 40
Answer:

TRIGGER IS AN SELF CONTAINED BLOCK OF STATEMENTS.
TRIGGER IS AN FIRED WHEN AN INSERT,UPDATE ,DELETE STATEMENTS.
TRIGGER IS APPLIED FOR DML STATEMENTS IN SQL SERVER 2000/2005
SQL SERVER 2008 DDL STATEMENTS ALSO SUPPORT TRIGGER

TYPES OF TRIGGER:

THERE ARE TWO TYPES OF TRIGGER AVAILABLE IN SQLSERVER
1) INSTEAD OF TRIRRGER
2) AFTER TRIGGER

REGARDS


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Muhsinathk on: 9/13/2012 | Points: 10
A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

SQL Server 2000 has many types of triggers:

1. After Trigger
2. Multiple After Triggers
3.Instead Of Triggers
4. Mixing Triggers Type
Posted by: Muhsinathk on: 9/13/2012 | Points: 10
Differences between storedprocedures and triggers
1. When you create a trigger you have to identify event and action of your trigger but when you create s.p you don't identify event and action

2.Trigger is run automatically if the event is occured but s.p don't run automatically but you have to run it manually

3. Within a trigger you can call specific s.p but within a sp you cannot call atrigger

4.Trigger execute implicitly whereas store procedure execute via procedure call from another block.

5.We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't call a trigger from these files.

6. Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.

Login to post response