for each record updated

Currently in my database I have Widgets. These Widgest can be linked
together through keys in a Link table. Think in terms of hierarchy. A
parent Widget has child Widgets linked to it. These child Widgets when
linked take on properties(field values) of the parent Widgets. I have a
trigger that fires when a property(field) is changed in the parent
Widget that changes that property(field) in all the child Widgets. I
want to add to that trigger so that the trigger will also insert a new
record into a log table for each record updated. Here is some example
code:
SELECT @.widget_id = widget_id, @.status = status
FROM inserted
BEGIN
update widget
set status = @.status, updated_date = @.current_date
where widget_id in (select linked_id
from link
where topic = 'LINK'
and log_type = 'CLONE'
and id = @.widget_id)
and defect_status != 'Complete'
END
For each of these children widgets updated I want to insert a record
into a log table with the widget_id and the linked_id as fields. Anyone
have any ideas. I hope this is somewhat clear.
JakeHello Jake !
To direct you to the right solution some DDL would be very nice,
because we don=B4t know your table structure and how the data is stored.
http://www.aspfaq.com/5006
After posting we will be pleased to help you :-D
HTH, Jens Suessmeyer.|||> SELECT @.widget_id = widget_id, @.status = status
> FROM inserted
Triggers are in their essence set-based. You just made yours lose that
wonderful ability.
You don't need variables to propagate changes from one table to another:
update <destination_table>
set <dest_column_1> = <src_column_1>
..
from inserted
<join> <destination_table>
on <destination_table>.<common_key> =
inserted.<common_key>
ML