Microsoft SQL Server forum - Dynamic If Update() in Trigger

Author

Dynamic If Update() in Trigger - Urgent!

Tolga Yaramis via SQLMonster.com

2005-06-29, 3:23 am

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.webservertalk.com

David Portas

2005-06-29, 3:23 am

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
--

Sponsored Links