updated column on the same table automatically [message #665402] |
Mon, 04 September 2017 05:19 |
|
mist598
Messages: 1195 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 #665469 is a reply to message #665442] |
Wed, 06 September 2017 12:42 |
Bill B
Messages: 1971 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 |
Solomon Yakobson
Messages: 3286 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 #665504 is a reply to message #665475] |
Thu, 07 September 2017 11:58 |
Bill B
Messages: 1971 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
|
|
|