pl/sql trigger
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: pl/sql trigger

  1. #1
    Join Date
    Sep 2000
    Posts
    7

    Exclamation

    i need some help i am a dba not a programmer.

    i am creating a trigger that will update table a after an insert or update . this trigger will update 2 columns to equal table b when table a primary key = table b primary key.


    create or replace trigger test
    after insert or update on table a
    for each row
    begin
    update table a
    set tablea.column1 = tableb.column1,
    tablea.column2 = tableb.column2
    where tablea.idno = tableb.idno
    end;


  2. #2
    Join Date
    Jul 2000
    Posts
    296
    Trigger to update table A after update or delete on table B:

    create or replace trigger test
    after insert or update on b
    for each row
    begin
    update a
    set a.column1=:new.column1
    , a.column2=:new.column2
    where a.idno=:new.idno;
    end;


  3. #3
    Join Date
    Jul 2000
    Posts
    296
    after an insert or update, not after update or delete.

  4. #4
    Join Date
    May 2000
    Location
    Richardson, Texas, USA
    Posts
    39
    This is potentially a dangerous piece of code since this will work recursively (fire the trigger from inside the trigger).

    What you should do is write a before insert/update trigger and set the new value for the columns, e.g.
    ...
    v_column1 := NULL;
    v_column2 := NULL;
    select column1, column2
    into v_column1, v_column2
    from tableb
    where idno = :new.idno;

    IF v_column1 IS NOT NULL THEN
    :new.column1 := v_column1;
    END IF;
    IF v_column2 IS NOT NULL THEN
    :new.column2 := v_column2;
    END IF;

    ....

    I hope you get the idea now.

    Thanks.

    Syed

Posting Permissions

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



Click Here to Expand Forum to Full Width