I have faced lots of problem during the interview so i have open this blog to help others as well as my self. Today itself i have created this blog so from tomorrow onwards i will update this blog with one subject daily. If u have any queries or request in any subject regarding .net then please post ur doubt here and i will try to give a better solution to u.

Thanks

Krishna

********************************** **************

Today' topic is about Trigger. The following material that i am going to share with u from a website. So thank to that person who has originally written this. The details are as follows:

Triggers in Sql server 2000?

Triggers are special types of store procedures, that are defined to execute automatically after or in place of a data modification. Triggers can not be created on the temporary tables. There are two types of triggers in sql server. After trigger and In Stead of trigger.

After Trigger:
An After trigger is a trigger that gets executed automatically before the transaction is committed or rolled back.

AFTER triggers gets executed automatically after the PK and FK constraints.

A table can have several After Triggers for each of the command (Insert, Update, Delete). If a table has multiple After triggers then using sp_settriggerorder u can set which trigger will fire first and which trigger will fire last but the remaining triggers will be executed randomly.

An After trigger can be created only on tables not in views. Triggers can be encrypted and once it is encrypted, the definition can not be viewed by anyone. even by the owner of the trigger.
e.g.
CREATE TRIGGER Triggername
on TableName
FOR Command(Insert/Delete/Update)
AS
Print ('After trigger executed')
GO

--COMMAND:
BEGIN TRANSACTION
DECLARE @Error INT

INSERT into (, ..) VALUES(, ..)

SET @Err= @@Error

IF @Err=0
BEGIN
ROLLBACK TRANSACTION
PRINT 'ROLLBACK TRANSACTION'
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT 'Commit TRANSACTION'
END

Instead of Trigger:
A Trigger which gets executed automatically in place of triggering action like Insert, Update, Delete is called Instead of Trigger.

This trigger gets executed automatically before the primary key and foreign key constraints are checked.

Instead of trigger can be created on views where as we can not create an after trigger on views.

A View or Table can have only one Instead of trigger for each command Insert/Update/Delete.
e.g.

Create Trigger TriggerName

On TableName

INSTEAD OF Command(Insert/Update/Delete)

AS

BEGIN

Print ('INSTEAD OF Trigger [trgInsteadInsert] – Trigger executed !!')

END

--COMMAND

INSERT INTO USER_DETAILS(USERID, FNAME,LNAME, MNAME, EMAIL)

VALUES(100, 'FName','LName','MName','test@test.com')

NOTE**: Views can be used to insert/update/delete the data in the multiple tables using Instead of Trigger.

Sp_helptrigger store procedure is used to know which trigger is associated with the given table and what type of trigger is that.
****************************************************