Click here to Skip to main content
15,566,394 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
How to add the extended properties on Trigger in SQL Server 2008 R2.
I want to add "MS_Description" on a trigger.
Posted

Use below query to add the MS_Desciption to a Trigger:

EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'TRIGGER DESCRIPTION',
@level0type = N'SCHEMA', @level0name = dbo,
@level1type = N'TABLE', @level1name = TABLENAME,
@level2type = N'TRIGGER', @level2name = TRIGGERNAME;

GO
 
Share this answer
 
 
Share this answer
 
SQL
CREATE DATABASE EMPLOYEES
USE EMPLOYEES

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

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

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

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

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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900