Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69

    Unanswered: How to create a trigger that updates a row in another table

    Hi,

    I try to create a trigger that behaves like described below:

    Table A (col p,q) p=column with productcode
    Table B (col r,s) r=column with same productcode as table A

    When updating column q from table A, column s from table B should get the same value when a row is updated with the same productcode.

    Can anyone help me how to do this (in SQL code)?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd try with UPDATE.

    Test case:
    Code:
    SQL> create table tab_a (p number, q number);
    
    Table created.
    
    SQL> create table tab_b (r number, s number);
    
    Table created.
    
    SQL> insert all
      2    into tab_a values (1, 100)
      3    into tab_a values (2, 200)
      4    into tab_b values (1, 40)
      5    into tab_b values (2, 50)
      6  select * from dual;
    
    4 rows created.
    
    SQL> select * from tab_a;
    
             P          Q
    ---------- ----------
             1        100
             2        200
    
    SQL> select * from tab_b;
    
             R          S
    ---------- ----------
             1         40
             2         50
    Trigger:
    Code:
    SQL> create or replace trigger trg_a
      2    after update of q on tab_a
      3    for each row
      4  begin
      5    update tab_b b set
      6      b.s = :new.q
      7      where b.r = :new.p;
      8  end;
      9  /
    
    Trigger created.
    Update:
    Code:
    SQL> update tab_a set q = 101 where p = 1;
    
    1 row updated.
    The result:
    Code:
    SQL> select * from tab_a;
    
             P          Q
    ---------- ----------
             1        101
             2        200
    
    SQL> select * from tab_b;
    
             R          S
    ---------- ----------
             1        101
             2         50

  3. #3
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    Hi Littlefoot,

    This works.
    However, with SQL directly on de database it does but when amending it by the ERP software it doesn't

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Impossible, a trigger will work no matter what is updating the table. Its possible that the ERP software didn't do a commit;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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