Using Triggers In SQL Server /Implementing Trigger In SQL Server

Using Triggers In SQL Server /Implementing Trigger In SQL Server

A trigger is used to execute a bach of sql code when a specific event is fired. There are two types of trigger

1>AFTER TRIGGER.

2>INSTEAD TRIGGER.

AFTER TRIGGER is used after a specific event fired.An AFTER trigger is a trigger that gets executed automatically before the transaction is committed or rolled back.

A trigger which gets executed automatically in place of triggering actions i.e., INSERT, DELETE and UPDATE is called an INSTEAD OF trigger.INSTEAD OFtriggers gets executed automatically before the Primary Key and the Foreign Key constraints are checked, whereas the traditional AFTER triggers gets executed automatically after these constraints are checked.

 

How to execute TRIGGER

First Open the Enterprice manager

1)Start -> Programs -> Microsoft SQL Server -> Enterprise Manager.

2)Expand Sql Server Group->Expand Database->Click on Tables .

3)Right click on the table and choose All Tasks -> Manage Triggers.This will open the trigger properties window, which allows us to create a new trigger:

The syntaxt of the Create Trigger is shown bellow.

1)An example of fire TRIGGER After an INSERT

CREATE TRIGGER [rndTrigger_insert] ON [dbo].[rnd_store_procedure]

FOR INSERT

AS

DECLARE @FName VARCHAR(255)

DECLARE @LName VARCHAR(255)

DECLARE @Address VARCHAR(255)

DECLARE @StoredID BIGINT

SELECT @StoredID = (SELECT PKID FROM Inserted)

SELECT @FName = (SELECT FName FROM Inserted)

SELECT @LName = (SELECT LName FROM Inserted)

SELECT @Address = (SELECT Address FROM Inserted)

INSERT INTO rnd_trigger (StoredID,FName,LName,Address) VALUES (@StoredID,@FName,@LName,@Address)

In the above example it is shown rndTrigger object is created and it is fired when data inserted into table rnd_store_procedure.

When the TRIGGER is fired data also inserted into table rnd_trigger.

2) An example of fire TRIGGER After an DELETE.

CREATE TRIGGER [rndTrigger_delete] ON [dbo].[rnd_store_procedure]

FOR DELETE

AS

DECLARE @PKID BIGINT

DECLARE @Status BIT

SELECT @PKID = (SELECT PKID FROM Deleted)

SELECT @Status = (SELECT Status FROM Deleted)

if(@Status = 1)

BEGIN

RETURN

END

DELETE FROM rnd_trigger WHERE StoredID = @PKID

In the above example it is shown rndTrigger object is created and it is fired when data deleted from table rnd_store_procedure.

When the TRIGGER is fired it is checked the deleted record status is true or false.

If deleted record status is false then corresponding record also deleted from rnd_trigger.

3) An example of fire TRIGGER After an UPDATE.

CREATE TRIGGER [rndTrigger_update] ON [dbo].[rnd_store_procedure]

FOR UPDATE

AS

DECLARE @PKID BIGINT

DECLARE @FName VARCHAR(255)

DECLARE @LName VARCHAR(255)

DECLARE @Address VARCHAR(255)

IF NOT UPDATE(FName) AND NOT UPDATE(LName)

BEGIN

RETURN

END

SELECT @PKID = (SELECT PKID FROM Inserted)

SELECT @FName = (SELECT FName FROM Inserted)

SELECT @LName = (SELECT LName FROM Inserted)

SELECT @Address = (SELECT Address FROM Inserted)

 

UPDATE rnd_trigger set FName = @FName,LName = @LName,Address = @Address

WHERE StoredID = @PKID

In the above example it is shown rndTrigger object is created and it is fired when rnd_store_procedure is updated.

When the TRIGGER is fired it is checked the FName AND LName from record weather being modified or not.If record is being modified then record from table rnd_trigger also modified.