Home » SQL & PL/SQL » SQL & PL/SQL » updated column on the same table automatically (11.2.0.4.0 - 64bit Production)
updated column on the same table automatically [message #665402] Mon, 04 September 2017 05:19 Go to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
Hi All,

Thanks for your help!

The quantity column should be updated automatically on remaining org_id=20, 30 based on the item if the data is inserted/updated on org_id=10 on the same table.

SELECT 10 org_id, 'ABC' item, 10 quantity
  FROM DUAL
UNION ALL
SELECT 20 org_id, 'ABC' item, NULL quantity
  FROM DUAL
UNION ALL
SELECT 30 org_id, 'ABC' item, NULL quantity
  FROM DUAL
UNION ALL
SELECT 10 org_id, 'BBC' item, 10 quantity
  FROM DUAL
UNION ALL
SELECT 20 org_id, 'BBC' item, NULL quantity
  FROM DUAL
UNION ALL
SELECT 30 org_id, 'BBC' item, NULL quantity
  FROM DUAL

could you please let me know if you required more details?

Thanks

[Updated on: Mon, 04 September 2017 05:22]

Report message to a moderator

Re: updated column on the same table automatically [message #665403 is a reply to message #665402] Mon, 04 September 2017 05:36 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
mist598 wrote on Mon, 04 September 2017 11:19

could you please let me know if you required more details?


We do.
Re: updated column on the same table automatically [message #665432 is a reply to message #665403] Tue, 05 September 2017 08:59 Go to previous messageGo to next message
Bill B
Messages: 1814
Registered: December 2004
Senior Member
i THINK WHAT YOU WANT TO DO IS EASY, BUT YOU HAVE TO GIVE MORE INFORMATION AND an example of what you want the output to look like. Your request is not clear.
Re: updated column on the same table automatically [message #665435 is a reply to message #665432] Tue, 05 September 2017 10:51 Go to previous messageGo to next message
mist598
Messages: 1188
Registered: February 2013
Location: Hyderabad
Senior Member
If the Quantity value for org_id 10 is updated, then 20,30 org_id Quantity values should be updated with same values.

Current output
--------------

ORG_ID ITEM QUANTITY
10 ABC 10
20 ABC
30 ABC

10 BBC 10
20 BBC
30 BBC

expected output
--------------

ORG_ID ITEM QUANTITY
10 ABC 10
20 ABC 10
30 ABC 10

10 BBC 10
20 BBC 10
30 BBC 10
Re: updated column on the same table automatically [message #665442 is a reply to message #665435] Wed, 06 September 2017 03:27 Go to previous messageGo to next message
cookiemonster
Messages: 13336
Registered: September 2008
Location: Rainy Manchester
Senior Member
If quantity needs to be the same for every record with the same item then this table should be split into two.
Re: updated column on the same table automatically [message #665469 is a reply to message #665442] Wed, 06 September 2017 12:42 Go to previous messageGo to next message
Bill B
Messages: 1814
Registered: December 2004
Senior Member
The following will do what you want.

CREATE TABLE TRIGGER_TEST
(
  ORG_ID    NUMBER,
  ITEM      CHAR(3 BYTE),
  QUANTITY  NUMBER
);

Load the following trigger

CREATE OR REPLACE TRIGGER TRIGGER_TEST_T1
AFTER INSERT OR UPDATE
ON TRIGGER_TEST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (
NEW.ORG_ID = 10
      )
declare
pragma autonomous_transaction;
BEGIN
UPDATE TRIGGER_TEST A
SET A.QUANTITY = :NEW.QUANTITY
WHERE A.ITEM = :NEW.ITEM
AND A.ORG_ID <> :NEW.ORG_ID;
commit;
END TRIGGER_TEST_T1;
/

Load your data

insert into trigger_test
SELECT 10 org_id, 'ABC' item, 10 quantity
  FROM DUAL
UNION ALL
SELECT 20 org_id, 'ABC' item, NULL quantity
  FROM DUAL
UNION ALL
SELECT 30 org_id, 'ABC' item, NULL quantity
  FROM DUAL
UNION ALL
SELECT 10 org_id, 'BBC' item, 10 quantity
  FROM DUAL
UNION ALL
SELECT 20 org_id, 'BBC' item, NULL quantity
  FROM DUAL
UNION ALL
SELECT 30 org_id, 'BBC' item, NULL quantity
  FROM DUAL;

Now when you change the quantity for any org_id = 10 record all the others will change to match it
Re: updated column on the same table automatically [message #665474 is a reply to message #665469] Wed, 06 September 2017 17:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2812
Registered: January 2010
Location: Connecticut, USA
Senior Member
Very bad suggestion.l

First of all, autonomous transaction doesn't see uncommitted main transaction changes. Therefore trigger will not work when same item is inserted for ORG_ID=10 and some other ORG_ID. Look at:

SQL> select * from trigger_test;

    ORG_ID ITE   QUANTITY
---------- --- ----------
        10 ABC         10
        20 ABC
        30 ABC
        10 BBC         10
        20 BBC
        30 BBC

6 rows selected.

SQL>

As you can see quantity for ORG_ID=20,30 is still NULL, while it should be set to 10. But worst part is it becomes non-transactional. Look what happens if we rollback the update:

SQL> update trigger_test
  2     set quantity = 99
  3   where org_id = 10
  4     and item = 'ABC'
  5  /

1 row updated.

SQL> select * from trigger_test
  2  /

    ORG_ID ITE   QUANTITY
---------- --- ----------
        10 ABC         99
        20 ABC         99
        30 ABC         99
        10 BBC         10
        20 BBC
        30 BBC

6 rows selected.

SQL> rollback
  2  /

Rollback complete.

SQL> select * from trigger_test
  2  /

    ORG_ID ITE   QUANTITY
---------- --- ----------
        10 ABC         10
        20 ABC         99
        30 ABC         99
        10 BBC         10
        20 BBC
        30 BBC

6 rows selected.

SQL> 

As you can see, item ABC quantity for ORG_ID=20,30 didn't rollback since it was already committed by autonomous transaction.
Anyway, this issue is poor design issue. It should be normalized into two tables:

1. ITEM(ITEM_ID,ITEM,QUANTITY)
2. ITEM_ORG(ITEM_ID,ORG_ID)

SY.
Re: updated column on the same table automatically [message #665475 is a reply to message #665474] Wed, 06 September 2017 17:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2812
Registered: January 2010
Location: Connecticut, USA
Senior Member
Actually, I stand corrected. Two table design I suggested will not work if it is allowed to directly change item quantity for ORG_ID != 10. Otherwise we have to use something like compound trigger.

SY.
Re: updated column on the same table automatically [message #665504 is a reply to message #665475] Thu, 07 September 2017 11:58 Go to previous message
Bill B
Messages: 1814
Registered: December 2004
Senior Member
I agree that it is not a good solution (I was being lazy). The old before statement trigger, on row trigger, after statement trigger using a package memory array is the way to go and is a solid fix. I will make up an example for the OP and insert it into this comment.

The following compound trigger will do what you want

CREATE OR REPLACE TRIGGER TRIGGER_TEST_T1
FOR DELETE OR INSERT OR UPDATE
ON TRIGGER_TEST
REFERENCING NEW AS NEW OLD AS OLD
COMPOUND TRIGGER
    TYPE T_row_item IS TABLE OF VARCHAR2 (3)
        INDEX BY BINARY_INTEGER;

    TYPE T_row_quantity IS TABLE OF NUMBER
        INDEX BY BINARY_INTEGER;


    T_tab_item             T_row_item;
    T_tab_item_empty       T_row_item;

    T_tab_quantity         T_row_quantity;
    T_tab_quantity_empty   T_row_quantity;
    Cnt                    NUMBER;
    I                      NUMBER;

    BEFORE STATEMENT
    IS
    BEGIN
        T_tab_item := T_tab_item_empty;
        T_tab_quantity := T_tab_quantity_empty;
    END BEFORE STATEMENT;

    AFTER EACH ROW
    IS
    BEGIN
        IF DELETING AND :old.Org_id = 10
        THEN
            I := T_tab_item.COUNT + 1;
            T_tab_item (I) := :old.Item;
            T_tab_quantity (I) := 0;
        ELSIF (UPDATING OR INSERTING) AND :new.Org_id = 10
        THEN
            I := T_tab_item.COUNT + 1;
            T_tab_item (I) := :new.Item;
            T_tab_quantity (I) := :new.Quantity;
        END IF;
    END AFTER EACH ROW;

    AFTER STATEMENT
    IS
    BEGIN
        -- UPDATE THE TABLE
        Cnt := T_tab_item.COUNT;
         IF Cnt > 0
        THEN
            FOR Pnt IN 1 .. Cnt
            LOOP
                UPDATE Trigger_test A
                   SET A.Quantity = T_tab_quantity (Pnt)
                 WHERE     A.Item = T_tab_item (Pnt)
                       AND A.Org_id <> 10;
            END LOOP;
        END IF;
    END AFTER STATEMENT;
END;
/

[Updated on: Thu, 07 September 2017 14:53]

Report message to a moderator

Previous Topic: phone type sql
Next Topic: Why some of my SQL are run slower in Oracle In-memory than it was before?
Goto Forum:
  


Current Time: Thu Nov 15 10:46:17 CST 2018