Re: Oracle Trigger

From: <rtproffitt_at_my-deja.com>
Date: Tue, 19 Oct 1999 16:19:20 GMT
Message-ID: <7ui5lq$ha6$1@nnrp1.deja.com>

You have an update trigger on a table,
which causes an update on the same
table, which fires the trigger again... an infinite loop. You must find a
way for the trigger to exit without updating on certain circumstances...Then, even though it will fire for many rows (because of the Update statement), many rows will exit and not fire another Update statement...

Can you explain the intended use better? It looks like you are saying,
If I update any column of any row in the table, then update the whole table, setting
VALUE equal to some hard code, everywhere there is a certain KEY4 with VALUE not
matching the desired hard code.

I don't see how you would want to use this type of a construct.

Below is a solution for the one row being updated...but that is not what you want, I think...

 CREATE OR REPLACE TRIGGER "CADBUSR"."SETURL"

     BEFORE UPDATE ON "CADBUSR"."CFGDB"
     FOR EACH ROW
   BEGIN
      If (:OLD.KEY4 = 'CA_Set_Url' ) and
         (:OLD.Value != 'http://machine:port/') Then
       :NEW.Value := 'http://machine:port/';
      End if;

   END; Robert Proffitt
Beckman Coulter

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 19 1999 - 11:19:20 CDT