Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Saturday, August 25, 2012

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


No comments :

Post a Comment