Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
How to add the extended properties on Trigger in SQL Server 2008 R2.
I want to add "MS_Description" on a trigger.
Posted 15-Mar-13 9:21am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 304
1 Sergey Alexandrovich Kryukov 255
2 Shweta N Mishra 216
3 Maciej Los 210
4 PIEBALDconsult 184
0 OriginalGriff 7,630
1 Sergey Alexandrovich Kryukov 7,022
2 DamithSL 5,586
3 Manas Bhardwaj 4,946
4 Maciej Los 4,525


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 22 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100