[quoted text, click to view]

Rows are not records; fields are not columns; tables are not files;
there is no sequential access or ordering in an RDBMS, so "first",
"second", "row #1 and #2", "next" and "last" are totally meaningless.
What is the logical relationship between these rows, and not their
location in physical storage?

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

[quoted text, click to view]

You ought to be able to update a well-defined subset of rows in ONE
update statement. In a file system, you would have to do procedural
code that hits one record at a time. You seem to be "faking it" in
triggers.

[quoted text, click to view]

This is one of the many reasons we avoid triggers and other procedural
code in favor of set-oriented code and DRI actions.

You can turn recursive/nested triggers off on your database/server,

http://www.devx.com/tips/Tip/30031

Regards
Mr Tea

[quoted text, click to view]

Hi,

I need the functionality to update a column in multiple rows in a table
based on the update of one of those rows in the same column.

E.g. a column 'status'. When the value of row 1 changes to 'accepted', the
value of the column 'status' in row 2 should change to 'accepted' too.
I've written a trigger that fires on an update of the column 'status'.
This trigger then finds the row that belongs to the row that is updated and
updates the value of that column in that row.

Problem: the trigger then fires again on the update of the row that I update
in my trigger.

How can I setup the trigger / database to do the update without firing off
the trigger again on the second update?

Thanks,

Roel

1. Check out the database option "recursive triggers".

2. In your trigger code, you can check against the TRIGGER_NESTLEVEL function.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


[quoted text, click to view]