Professional Web Applications Themes

Trigger: Insert if not exists? - Oracle Server

Hello Barry, I'd suggest you to put a referential constraint on the title_word table (in this case a primary key) and then trap any error of the kind DUP_VAL_ON_INDEX inside the trigger, that is wrapping up the INSERT statement: create or replace trigger <my_trigger> .... bla bla bla ... begin ... begin INSERT INTO title_words bla bla bla; exception when DUP_VAL_ON_INDEX then null; when others then raise; end; .... end; It looks simple to me, but to you ? Bye, Flavio "Barry Prentiss" <thebearNOSPAM.stanford.edu> wrote in message news:at5fqr$erd$1news.Stanford.EDU... > Hi, > I am trying to devise a trigger that inserts ...

  1. #1

    Default Re: Trigger: Insert if not exists?

    Hello Barry,
    I'd suggest you to put a referential constraint on the title_word table (in
    this case a primary key) and then trap any error of the kind
    DUP_VAL_ON_INDEX inside the trigger, that is wrapping up the INSERT
    statement:

    create or replace trigger <my_trigger>
    .... bla bla bla ...

    begin
    ...
    begin

    INSERT INTO title_words
    bla bla bla;

    exception
    when DUP_VAL_ON_INDEX then
    null;
    when others then raise;
    end;
    ....
    end;

    It looks simple to me, but to you ?

    Bye,
    Flavio





    "Barry Prentiss" <thebearNOSPAM.stanford.edu> wrote in message
    news:at5fqr$erd$1news.Stanford.EDU...
    > Hi,
    > I am trying to devise a trigger that inserts an object_id and a list of
    the
    > object's title_words into a title_words table. I would like to avoid
    > duplicate rows by ensuring that, even if the same title_word occurs many
    > times, only one insert for that object_id/title_word pair will occur. Is
    > there a simple way to do this?
    > [Oracle 8i, PL/SQL]
    > Thx in Advance,
    > Barry Prentiss
    > Stanford University
    >
    >

    FC Guest

  2. #2

    Default Re: Trigger: Insert if not exists?

    Thx Flavio,
    Exactly what I was looking for...
    Happy Holidays,
    Barry

    "FC" <flaviotin.it> wrote in message
    news:PKrJ9.2255$ab2.65876news1.tin.it...
    > Hello Barry,
    > I'd suggest you to put a referential constraint on the title_word table
    (in
    > this case a primary key) and then trap any error of the kind
    > DUP_VAL_ON_INDEX inside the trigger, that is wrapping up the INSERT
    > statement:
    >
    > create or replace trigger <my_trigger>
    > ... bla bla bla ...
    >
    > begin
    > ...
    > begin
    >
    > INSERT INTO title_words
    > bla bla bla;
    >
    > exception
    > when DUP_VAL_ON_INDEX then
    > null;
    > when others then raise;
    > end;
    > ...
    > end;
    >
    > It looks simple to me, but to you ?
    >
    > Bye,
    > Flavio
    >
    >
    >
    >
    >
    > "Barry Prentiss" <thebearNOSPAM.stanford.edu> wrote in message
    > news:at5fqr$erd$1news.Stanford.EDU...
    > > Hi,
    > > I am trying to devise a trigger that inserts an object_id and a list of
    > the
    > > object's title_words into a title_words table. I would like to avoid
    > > duplicate rows by ensuring that, even if the same title_word occurs many
    > > times, only one insert for that object_id/title_word pair will occur. Is
    > > there a simple way to do this?
    > > [Oracle 8i, PL/SQL]
    > > Thx in Advance,
    > > Barry Prentiss
    > > Stanford University
    > >
    > >
    >
    >

    Barry Prentiss Guest

Similar Threads

  1. method name exists, property value exists, calling method fails
    By Phil Powell in forum PHP Development
    Replies: 0
    Last Post: October 27th, 07:34 PM
  2. Replies: 3
    Last Post: September 30th, 09:24 PM
  3. Before update or delete trigger to insert ?
    By Cindy Gold in forum IBM DB2
    Replies: 2
    Last Post: August 12th, 01:38 PM
  4. Trigger - wont allow me to insert
    By pat in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 3rd, 05:51 PM
  5. Have trigger supply value for NOT NULL column on insert
    By Ian Boyd in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 3rd, 02:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139