Re: don't want trigger error to block insert

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Wed, 14 Jul 1999 20:27:35 GMT
Message-ID: <3790f255.19565633@inet16.us.oracle.com>

On Wed, 14 Jul 1999 13:01:18 -0700, Sarah Officer <officers_at_aries.tucson.saic.com> wrote:

>I created an insert trigger on one table so that whenever a record
>is inserted, it causes a record to be inserted into another table.
>It is possible that the record already exists in the second table,
>so inserting again violates a uniqueness constraint in table2. This
>causes the insert into the original table to fail.
>
>How can I write my trigger so that it will insert the record if it
>can, but it won't cause failure if the insert to table2 fails?
>

Use an exception block.

CREATE TRIGGER table1_insert_trigger
  AFTER INSERT ON table1
  FOR EACH ROW
  WHEN(new.master_flag = '1')
  BEGIN
    insert into table2(id, filename, category, description)     select :new.id, :new.table1_filename, 'image', 'image: ' || s.longname     from table3 s
    where s.id = :new.id;
  EXCEPTION
    when DUP_VAL_ON_INDEX then
      null;
  END;
/

>
>Thanks,
>
>Sarah Officer
>officers_at_aries.tucson.saic.com

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jul 14 1999 - 15:27:35 CDT