Re: Oracle Trigger

We have an application that updates the "value" for one row in a table upon start-up. In some installations of this application we do not want the updated value placed in the table, but wish it to remain as it was - original value to be defined in the trigger. We are wanting a trigger that any time this value is modified the trigger is called and resets the value back to the original value.

We need the trigger to execute After Update of the table - the only modification we want is

    set value = old.value

      where key4 = 'CA_Set_Url'
                  and  old.value != new.value

Below is the trigger we have implemented under SQL Server that we were wanting to recreate the functionality of under Oracle.

CREATE TRIGGER seturl

   ON cadbusr.cfgdb
FOR UPDATE
AS
  IF UPDATE(value)
    UPDATE cadbusr.cfgdb

      SET
        value = "http://machine_name.domain_name:port_no"
     where key4 = "CA_Set_URL"

I really appreciate your help.

Thanks,

Theran Crooks

rtproffitt_at_my-deja.com wrote in message <7ui5lq$ha6$1_at_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 - 15:18:15 CDT