Sql Server Trigger for Beginner

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




0 Comments

Featured Post

Understanding about Azure Region, Geographies, Availability Zones and Region Pairs

Introduction Microsoft Azure services are available in 53 regions around the world, with plans announced for 7 additional regions and 140+ a...

Subscribe to the FREE Weekly Newsletter to receive all the latest Microsoft Azure, AI, Xamarin

Popular Posts