[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4358
  • Last Modified:

To update a table triggerred by another table's change

Hi Experts,

I need a solution to update a table, the update is triggerred by another table's change.

1)Where there is any change in table 1, it will checking whether the column of 'ack' has value of '0'.
2)if so, it need to trace to table 4 through table 2 and 3 by key.
3)update the column of 'affected' in table 4 by value '0', original is '1'.


May I know what tech I should use, may you give some detail code or examples.

Thanks and Regards,

Turbot
0
turbot_yu
Asked:
turbot_yu
4 Solutions
 
MetanilCommented:
The syntax for an AFTER UPDATE Trigger is:

    CREATE or REPLACE TRIGGER trigger_name
    AFTER UPDATE
        ON table_name
        [ FOR EACH ROW ]
    DECLARE
        -- variable declarations
    BEGIN
        -- trigger code
    EXCEPTION
        WHEN ...
        -- exception handling
    END;

trigger_name is the name of the trigger to create.

Restrictions:

    * You can not create an AFTER trigger on a view.
    * You can not update the :NEW values.
    * You can not update the :OLD values.

Example:
CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
    ON orders
    FOR EACH ROW

DECLARE
    v_username varchar2(10);

BEGIN

    -- Find username of person performing UPDATE into table
    SELECT user INTO v_username
    FROM dual;

    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity_before,
       quantity_after,
       username )
    VALUES
     ( :new.order_id,
       :old.quantity,
       :new.quantity,
       v_username );

END;

0
 
ora_user2003Commented:
Turbot ,

    The simplest solution for you is provided by Metanil.

Lets say there are two tables A and B

A is the table which contains the column ack which u nee to check for the update of the second table
( Trigger is a database object which the oracle executes when the table on which it resides undergoes the action specified in the create statement of the trigger ...)


   u can write a trigger on table A as follows


CREATE OR REPLACE TRIGGER Trig_A
AFTER INSERT OR UPDATE
    ON A
    FOR EACH ROW

DECLARE
    v_username varchar2(10);

BEGIN
/*
u can refer to the columns of the table being updated using

    :NEW.ACK etc...

eg... if :new.ACK=0 then ......




*/


if inserting then
---- handle the case for insert into  A here





elsif updating then

---- handle the case for update  here

end if ;



END;



 Try it out and let me know
0
 
anand_2000vCommented:
yes the correct answer is triggers
however the points specified that :OLD and :NEW cannot be changed is wrong as shown by this example.

SQL> create or replace trigger anand before insert on dept
  2  for each row
  3  begin
  4  :new.dname:='TRAINING';
  5  end;
  6  /

Trigger created.

SQL> insert into dept values (50,'f','NEW YORK');

1 row created.

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 TRAINING       NEW YORK

SQL>
0
 
helpneedCommented:
hi

We illustrate Oracle's syntax for creating a trigger through an example

CREATE TABLE T4 (a INTEGER, b CHAR(10));

CREATE TABLE T5 (c CHAR(10), d INTEGER);

We create a trigger that may insert a tuple into T5 when a tuple is inserted into T4. Specifically, the trigger checks whether the new tuple has a first component 10 or less, and if so inserts the reverse tuple into T5:

CREATE TRIGGER trig1
    AFTER INSERT ON T4
    REFERENCING NEW AS newRow
    FOR EACH ROW
    WHEN (newRow.a <= 10)
    BEGIN
        INSERT INTO T5 VALUES(:newRow.b, :newRow.a);
    END trig1;

regards
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now