how to alter a trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to alter a trigger

Author

Topic

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-07-21 : 03:51:05

hi all,
i have a table tbl1 with 4 columns ,i wrote a update trigger(trg_update) to update the data in another table. i want to add a column in table tbl1 using a trigger at the same time i want the trigger trg_update should get alter.

here is the code to alter a trigger
------------------------------------
declare @l_sql varchar(2000),@l_sqltrg varchar(2000),@l_stdattribute varchar(50)

SET @L_SQL='ALTER TABLE DBO.TBL1 ADD '+@L_STDATTRIBUTE+' VARCHAR(50) NULL'
SELECT @L_SQL
EXECUTE (@L_SQL)

SET @L_SQLTRG='ALTER TRIGGER TRG_UPDATE ON [dbo].TBL1
FOR UPDATE IF UPDATE('+@L_STDATTRIBUTE+') BEGIN UPDATE TBL_STDATTRIBVALUE_CLIENT SET STDATTRIB_VALUE=B.'+@L_STDATTRIBUTE+' FROM TBL_STDATTRIBVALUE_CLIENT A INNER JOIN INSERTED B ON A.HIPL_ID=B.HIPL_ID AND A.STDATTRIBUTE='''+@L_STDATTRIBUTE+''' AND B.HIPL_ID IN(SELECT HIPL_ID FROM INSERTED)
END'
SELECT @L_SQLTRG
EXECUTE(@L_SQLTRG)

Am i doing any mistake in writing the dynamic sql, bcoz table tbl1 is alter perfectly but not the trigger trg_update

can anybody help me to solve this issue

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 03:56:20

Do you get any warnings or error messages?

This has to be one of the worst ideas ever for database design.
Why would you want to create a trigger dynamically?

E 12°55'05.25"
N 56°04'39.16"

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-07-21 : 04:10:31

As my table tbl1 grows and shrinks dynamically therefore i want a trigger should grows or shrinks using dynamic sql. Will it effect on performance if so give me the link that describes this issue.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 04:23:21

1) Grows vertical or horizontal?
2) A trigger is a trigger is a trigger. It doesn't complain about the number of records in a table.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources