469,626 Members | 883 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

Dynamic If Update() in Trigger - Urgent!

Hi All

I have a question about generating dynmamicly If Update() statement in a
trigger..

in My db, there is a table that holds some column names of an another table.
for example;

Columns Table-A: Col1, Col2, Col3, Col4,Col5

Table-B: Col2, Col5 (The selected columns of Table A)

Then, in the Trigger of Table-A I use;

Select name from syscolumns where id=object_id('Table-A')

fetch next from TableA_Cursor into @strColName

then, I used a statement like this..

if UPDATE(' + @strColName + ')

But it gives "incorrect syntax" error..

How can I write this line?

Thanks alot in advance...

--
Message posted via http://www.sqlmonster.com
Jul 23 '05 #1
1 3290
I don't believe it is possible to use IF UPDATE() dynamically, nor is
it necessary. It also doesn't really make much sense to reference IF
UPDATE() in a cursor since the result will be the same for every row.
Anyway you shouldn't use cursors in triggers - they just turn your
set-based update statements into row-based updates, which is bad in
principle from a design poiunt of view and generally performs very
poorly.

If you want your triggers to take account of table structure changes
then generate the trigger code dynamically at DESIGN time rather than
runtime.

If you need a trigger to act on what data has changed then join the
Inserted and Deleted virtual tables and compare the columns. IF UPDATE
doesn't tell you what changed, only which columns were referenced by
the update statement.

If you need more help, please post a fuller description of your problem
including DDL and sample data.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Jason | last post: by
3 posts views Thread by Mukesh | last post: by
3 posts views Thread by J055 | last post: by
1 post views Thread by markla | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.