>>Hi Borislav,
>>
>>The first line in the Using Nested Triggers answered my question
>>
An AFTER trigger does not call itself recursively unless the RECURSIVE_TRIGGERS database option is set. >>
>>So, since I didn't change this setting and use its default value, it worked.
>>
>>However, I guess, this is not a save approach.
>>
>>Is there any other way to change value in the field of a table when another field is changed? Say, in my case I want to always set Drop_Date to current date if I set status to 'D'.
>>
>>This was just an exercise, but I'd like to understand the concept.
>>
>>Thanks again for your help.
>
>You could use Trigger but you should check IF Status field is updated and IF you have 'D' in that updated field:
>
>.....
>IF UPDATED('Status') AND
> EXISTS (SELECT * FROM Updated WHERE Status = 'D')
> BEGIN
>
> END
>...
>
>(not tested)
Thanks for the UPDATE(column) function which I didn't know. But the question still remains - is it OK or not to UPDATE another column in the UPDATE trigger.
May be there is a way to find out current RECURSIVE_TRIGGERS option status and adjust it inside the trigger's code and restore back?
If it's not broken, fix it until it is.
My Blog