Re: Trigger Help

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/12/12
Message-ID: <36789e5a.4258052_at_192.86.155.100>#1/1


A copy of this was sent to "Jeff Hunter" <jeffh_at_btitelecom.net> (if that email address didn't require changing) On Fri, 11 Dec 1998 15:38:27 -0800, you wrote:

>I want to write a trigger that will update non-primary key fields in a table
>on an insert. For example, I have a table called CUST that has two fields,
>ID and NAME. The ID field is the primary key. The CUST table might look
>like:
>
>ID NAME
>1 Scooby Doo
>2 Scrappy Doo
>3 Shaggy
>
>
>When I issue "insert into CUST values (1,'Freddy'); I want the table to look
>like:
>
>ID NAME
>1 Freddy
>2 Scrappy Doo
>3 Shaggy
>
>Any suggestions?
>

so, you want to change an INSERT into an UPDATE (maybe, if the row exists).

What version of the database do you have? If its O8, then you can do this. If O7 -- I can't really think of a way to do this.

SQL> create table t_tbl ( id int primary key, name varchar(30) );

Table created.

SQL>
SQL> create or replace view t as select * from t_tbl;

View created.

SQL>
SQL> create or replace trigger t_trigger   2 instead of insert on t
  3 for each row
  4 begin
  5 insert into t_tbl values ( :new.id, :new.name );   6 exception

  7          when DUP_VAL_ON_INDEX then
  8                  update t_tbl set name = :new.name where id = :new.id;
  9 end;
 10 /

Trigger created.

SQL>
SQL> insert into t values ( 1, 'Scooby Doo' );

1 row created.

SQL> insert into t values ( 2, 'Scrappy Doo' );

1 row created.

SQL> insert into t values ( 3, 'Shaggy' );

1 row created.

SQL>
SQL> select * from t;

        ID NAME

---------- ------------------------------
         1 Scooby Doo
         2 Scrappy Doo
         3 Shaggy

SQL>
SQL> insert into t values ( 1, 'Freddy' );

1 row created.

SQL>
SQL> select * from t;

        ID NAME

---------- ------------------------------
         1 Freddy
         2 Scrappy Doo
         3 Shaggy

SQL>   Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.
Received on Sat Dec 12 1998 - 00:00:00 CET

Original text of this message