Sql Server Trigger for Beginner

                                                    Trigger

A trigger is a SQL procedure  that initiates an action  .when an event (INSERT,UPDATE,DELETE) occures

Triggers can be viewed as similar to stored procedure in that both consist of procedural logic that is stored at the database level.

STEP 1:

CREATE TABLE  Student(SID int IDENTITY, SNAME varchar(10))

create a trigger that displays the count student table when a row is inserted into the table to which it is attached.

STEP 2:

CREATE TRIGGER tr_student_insert

ON STUDENT

FOR INSERT

AS

SELECT COUNT(*) AS 'NUMBER OF RECORD' FROM  Student

STEP 3:

INSERT INTO Student VALUES('SUTHAHAR')

RESULT:

Use the inserted and deleted Tables

STEP 1:

CREATE TABLE  STUMARK(SID int IDENTITY, MARK NUMERIC(10))

STEP 2:

CREATE TRIGGER tr_STUMARK_insert

ON STUMARK

FOR INSERT

AS

IF((SELECT MARK FROM  inserted)  < 40)

BEGIN

PRINT 'FAIL'

END

ELSE

BEGIN

PRINT 'PASS'

END

STEP 3:

INSERT INTO STUMARK VALUES(78)

OUTPUT:

AFTER UPDATE

CREATE TRIGGER tr_STUDENT_UPDATE

ON STUDENT

AFTER UPDATE

AS

AFTER DELETE

CREATE TRIGGER tr_STUDENT_DELETE

ON DELETE

AFTER UPDATE

AS

INSTEAD  OF TRIGGERS

CREATE TRIGGER tr_STUDENT_INSERT_InsteadOf

ON STUDENT

INSTEAD OF INSERT

AS

PRINT 'Updateable Views are Messy'

go