sdbeames wrote:

>Is it possible to temporarily disable an after update trigger
>from within itself to prevent recursion where the trigger
>causes further edits to the same table?
>
>In my particular case it seems difficult to solve this by
>just looking at the incoming field values and bypassing the
>offending code.
>
>Thanks,
>Steve
>
>

Inside the trigger no, maybe with execute statement, but it will be far
unrecommended. Anyway but how will you enable the trigger again if the
trigger won't be fired ?

I would creaet a flag column on the table, and set the value to
processed on the view, and in the view check if the flag column is
marked so don't procecess it again.

create trigger MyTrigger for MyTable after update as
begin
if(new.MyFlag <> 'T') then begin
<do some process here>
new.MyFlag = 'T';
end;
end

or insert the PK of the processed record in a LogTable and search for it
to discover if the record has been already processed or not.

create trigger MyTrigger for MyTable after update as
begin
if not exists(Select 1 from LogTable where PKColumn = New.PKColumn)
then begin
<do some process here>
insert into LogTable(New.PKColumn);
end;
end

You will need to clean the FlagColumn or LogTable at the end.

If you use the FlagColumn approach you could store the
Current_Transaction value to it and check it the new.FlagTable =
Current_Transaction, if it is equals then you have already processed it,
otherwise, you don't so you process, using the current_transaction
approach you wil not need to clean nothing at the end of the process.

HTH

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 15/03/2005