Re: Oracle Trigger

create or replace trigger seturl after update of table for each row begin
if :new.value <> :old.value then

   if :new.key4 = 'CA_SET_URL'
   then :new.value = :old.value
   end if;
end if;
end;
/

A far better idea would be create a view with check option, where this particular row is excluded, and users only getting access to that view: create or replace view newview
as select *

    from mytab
    where key4 <> 'CA_SET_URL'
with check option.
You can refer to package variables in a view, so if you could store that particular value in a package variable, you need only 1 view.

Hth,

Sybrand Bakker, Oracle DBA

--
Sybrand Bakker, Oracle DBA
Theran Crooks <crooks_at_globeset.com> wrote in message news:7uijve$5tu$1_at_onion.globeset.com...
> 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 - 16:37:45 CDT