I have condition where i need to update table x1 with the value of e1,e2,and e3. Assume e1 is primary key. When data gets modified on X2, I need to pull max of e2 and max of e3 to update the table X1. I wrote trigger like this however getting mutating table error. I don't want to use PRAGMA AUTONOMOUS TRANSACTION. Your help would be appriciated.
create or replace TRIGGER TR_UPDATE_ON_X2
AFTER INSERT OR UPDATE OR DELETE ON X2
FOR EACH ROW
DECLARE
cnt NUMBER;
E1 VARCHAR2(20 BYTE);
E2 NUMBER(3,0);
E3 VARCHAR2(50 BYTE);
BEGIN
IF INSERTING OR UPDATING THEN
SELECT COUNT(1) INTO CNT FROM X1 WHERE E1 = :NEW.E1;
IF(CNT =0)THEN
INSERT INTO X1 (E1, E2, E3) VALUES(:NEW.E1,:NEW.E2,:NEW.E3);
ELSE
SELECT E1,E2,E3 INTO E1, E2, E3 FROM X1 WHERE E1 = :NEW.E1;
IF(:NEW.E1 > E1) THEN
E1 := :NEW.E1;
END IF;
IF(:NEW.E2 > E2) THEN
E2 := :NEW.E2;
END IF;
IF(:NEW.E3 > E3) THEN
E3 := :NEW.E3;
END IF;
UPDATE X1 SET E1 = E1, E2 = E2, E3 = E3 WHERE E1 = :NEW.E1;
END IF;
ELSIF DELETING THEN
SELECT COUNT(1) INTO CNT FROM X2 WHERE CONTRACTORSOIDENTIFIER = :NEW.CONTRACTORSOIDENTIFIER;
IF(CNT != 0)THEN
SELECT E1,MAX(E2),MAX(E3) INTO E1, E2, E3 FROM X2 WHERE E1 = :NEW.E1 GROUP BY X2.E1;
IF(:NEW.E1 > E1) THEN
E1 := :NEW.E1;
END IF;
IF(:NEW.E2 > E2) THEN
E2 := :NEW.E2;
END IF;
IF(:NEW.E3 > E3) THEN
E3 := :NEW.E3;
END IF;
UPDATE X1 SET E1 = E1, E2 = E2, E3 = E3 WHERE E1 = :NEW.E1;
ELSE
DELETE FROM X1 WHERE E1 = :NEW.E1;
END IF;
END IF;
END;
So, there can be many rows per e1 in x2. And for each e1 that exists in x2 there exists one row in x1. The x1 row contains the maximum values for e2 and e3 per e1.
Your INSERTING OR UPDATING part looks okay, apart from naming your variables identical to your columns. As William Robertson already pointed out in the request comments, SET E1 = E1 just means keep the value as is. The DBMS doesn't guess that the second E1 is supposed to be the other E1. It is common to precede variables with v_ to make them distinct from column names.
Your DELETING has a main issue. The problem is that by deleting an x2 row, the x1 maximums must be evaluated anew for the e1. This means that we must select from x2 while the table is in the process of changing. (As a delete statement can affect more than one row, AFTER ROW does not necessarily mean AFTER STATEMENT, so we may be in the middle of changes and can not deterministicly say what's yet in the table and what not.)
The solution to this is a compound trigger. This trigger type combines before/after row and before/after statement. So you can look at the rows, see which e1 are affected and then after all the rows processing you update x1 for all affected e1.
Here is a simple trigger (that certainly leaves room for optimization :-)
CREATE OR REPLACE TRIGGER tr_update_x1_on_x2
FOR INSERT OR UPDATE OR DELETE ON x2
COMPOUND TRIGGER
v_array SYS.ORA_MINING_NUMBER_NT := SYS.ORA_MINING_NUMBER_NT();
AFTER EACH ROW IS
BEGIN
-- Add e1 to the array for a later insert/update/delete in x1
v_array.EXTEND;
v_array(v_array.COUNT) := COALESCE(:new.e1, :old.e1);
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
-- Delete rows from x1 where the e1 no longer exists in x2
DELETE FROM x1
WHERE e1 IN (select * from table(v_array))
AND e1 NOT IN (select e1 from x2);
-- Insert/update x1 rows with the new maximums found in x2
MERGE INTO x1
USING
(
SELECT e1, MAX(e2) AS max_e2, MAX(e3) AS max_e3
FROM x2
WHERE e1 IN (select * from table(v_array))
GROUP BY e1
) src ON (src.e1 = x1.e1)
WHEN MATCHED THEN
UPDATE SET e2 = src.max_e2, e3 = src.max_e3
WHEN NOT MATCHED THEN
INSERT (e1, e2, e3) VALUES (src.e1, src.max_e2, src.max_e3)
END AFTER STATEMENT;
END tr_update_x1_on_x2;