Results 1 to 3 of 3

Thread: updatehelp

  1. #1
    Join Date
    Oct 2002
    Posts
    36

    Unanswered: updatehelp

    1) can we update the values of two tables at a time using a common
    field in both tables

    for eg:

    update T1,T2 set T1.C1 = T2.C1
    where T1.PK = 1 and T2.PK in (1,2)

    is it possible??

    2)can i write a trigger to update two tables at a time??

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: updatehelp

    Originally posted by vadlamanibujji
    1) can we update the values of two tables at a time using a common
    field in both tables

    for eg:

    update T1,T2 set T1.C1 = T2.C1
    where T1.PK = 1 and T2.PK in (1,2)

    is it possible??

    2)can i write a trigger to update two tables at a time??
    1) No you can only update one table at a time in SQL (without triggers).
    Mind you, I don't understand your example. This example seems to be trying to update just one record in one table (T1) with data from 2 records in T2. That doesn't make sense!

    You can update a view that has more than one base table, but you can still only update ONE of the base tables, which must be "key preserved".
    For example, this is valid:

    update
    ( select emp.*
    from emp, dept
    where emp.deptno = dept.deptno
    and dept.dname = 'SALES'
    )
    set ename = UPPER(ename);

    6 rows updated.

    But not this:

    update
    ( select dept.*
    from emp, dept
    where emp.deptno = dept.deptno
    and emp.ename='KING'
    )
    set dname = UPPER(dname);

    ORA-01779: cannot modify a column which maps to a non key-preserved table

    Briefly, "key-preserved" means that the PK of the base table is also unique for the view - in these examples EMP is key preserved, but DEPT is not (since many EMPs may have same deptno).

    2) Yes, you can do more or less whatever you like via triggers! For example:

    CREATE TRIGGER t1_aur AFTER UPDATE OF c1 ON t1
    FOR EACH ROW
    BEGIN
    UPDATE t2 SET c1 = :NEW.c1;
    END;

    You can also use an INSTEAD OF trigger on a view to convert an INSERT/UPDATE/DELETE of the view into one or more INSERTS/UPDATES/DELETES on one or more tables (whether they are base tables of the view or not). The only sensible use of this is to update the base tables of the view, but there's nothing to stop you doing whatever you please - e.g.

    CREATE TRIGGER t2 INSTEAD OF INSERT ON v1
    FOR EACH ROW
    BEGIN
    DELETE t1 WHERE t1.created_date < TRUNC(SYSDATE);
    UPDATE t2 SET counter = counter+1;
    INSERT INTO t3 (c1) VALUES (:NEW.c1);
    END;

  3. #3
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    1

    How to create a trigger to monitor update on one table and fire update on the second

    Hi all,

    Will you please give me a help to resolve my issue. Suppose we have two tables: item (item_id NUMBER PK, tmpstamp DATE) as a parent and the engine as a subtype of item (item_id NUMBER FK, mileage NUMBER, cycles NUMBER).

    Problem: Each time when user updates either mileage or cycles in engine table, the tmpstamp column in the item must be updated.

    I've started creating the trigger and I've stuck... I know that I can create a stored procedure for the item update and call that sp in the trigger's body passing the item_id as a parameter.

    But... Is there a more simple way to manage that situation ?

Posting Permissions

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