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.
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')
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)
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
STEP 1:
CREATE TABLE Student
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
STEP 3:
INSERT INTO Student VALUES
RESULT
Use the inserted and deleted Tables
STEP 1:
CREATE TABLE STUMARK
STEP 2:
CREATE TRIGGER tr_STUMARK_insert
ON STUMARK
FOR INSERT
AS
IF
BEGIN
PRINT 'FAIL'
END
ELSE
BEGIN
PRINT 'PASS'
END
STEP 3:
INSERT INTO STUMARK VALUES
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'
Posts
0 Comments