TRIGGERS IN SQL SERVER 2008

TRIGGERS IN SQL SERVER 2008

1.Open sql server 2008,

2.open new query

code

STEP 1:

CREATE DATABASE TRIGGERS_EMP

 USE TRIGGERS_EMP

CREATE TABLE SAL_INFO(GRADE CHAR(2),SAL NUMERIC(8,2))

STEP 2:

INSERT INTO SAL_INFO VALUES('A',50000)

INSERT INTO SAL_INFO VALUES('B',40000)

INSERT INTO SAL_INFO VALUES('C',30000)

SELECT * FROM SAL_INFO

STEP 3:

CREATE TABLE EMP(EMP_NO INT, EMP_NAME VARCHAR(50),DEPT_NO CHAR(5),GRADE CHAR(2), BSAL NUMERIC(8,2))

SELECT * FROM EMP

STEP 4:

CREATE TRIGGER TR_EMP ON EMP

FOR INSERT,UPDATE

AS

DECLARE @SAL NUMERIC(8,2)

SELECT @SAL = SAL_INFO.SAL FROM SAL_INFO,INSERTED

WHERE INSERTED.GRADE = SAL_INFO.GRADE

UPDATE EMP SET BSAL = @SAL FROM EMP,INSERTED

WHERE EMP.EMP_NO =  INSERTED.EMP_NO

STEP 5:

INSERT INTO EMP VALUES(1001,'SAJU','D101','A',NULL)

INSERT INTO EMP VALUES(1002,'MUJI','D102','B',NULL)

INSERT INTO EMP VALUES(1003,'NIYAS','D103','C',NULL)

SELECT * FROM EMP

OUTPUT: