adding data from two different tables using a trigger

Question

I created three tables A (id, name, date, realnumber, integer), B (id, name, date, realnumber, integer), and C which is identical to table A. It only has two more columns called integerB and sequence s. I want to create a trigger which would fire after insert on table B for each row input so that it saves the referenced row of Table A and adds integer from input row of table B in column integerB of table C. If the row already exists in Table C only integerB should be added. When it comes to sequence s, next value is added with first insert of row of table A.

Simple explanation: Table C is a copy of table A with two additional columns: integerB and sequence. The point of the trigger is to add new rows from table A without repetition, integerB from table B(integer in table B) and sequence should start with 1 and increment by 1. If the row in table A is repeated then only integerB should be updated.

I did not work with triggers that much, so I am not sure how to solve the problem when I have to insert data from multiple tables. Here is my trigger.

CREATE OR REPLACE TRIGGER trig1
AFTER INSERT ON B
FOR EACH ROW
INSERT INTO C (integerB) VALUES (NEW.integer); 
INSERT INTO C (id, name, date, realnumber)
SELECT a.id, a.name, a.date, a.realnumber FROM A a; 
END;
/

Jameson_uk · Answer 1 · 2023-01-24T08:15:51.070

First off you really need to use better column and table names as a lot of these are reserved words... This makes everything far more complicated than it needs to be.

It isn't entirely clear what you want to do but it seems that if someone was to insert a record with ID = 1 into B then you want to get the values from A for ID = 1 and store them in C along with the integer value inserted into B

In which case you want to use an MERGE statement (UPSERT) in your trigger and something like

CREATE OR REPLACE TRIGGER T1
AFTER INSERT ON B
FOR EACH ROW
BEGIN
  MERGE INTO C C
  USING (SELECT * FROM A WHERE ID = :NEW.ID) A
  ON (C.ID = :NEW.ID)
  WHEN MATCHED THEN UPDATE
    SET C.INTEGERB = :NEW.INTEGER,
        C.SEQUENCE = C.SEQUENCE + 1
  WHEN NOT MATCHED THEN 
    INSERT (ID, NAME, DATE, REALNUMBER, INTEGER, INTEGERB, SEQUENCE)
    VALUES (A.ID, A.NAME. A.DATE, A.REALNUMBER, A.INTEGER, :NEW.INTEGER, 0);
 
END;
/

For sequence this has been set to 0 when a new record is inserted into C and then incremented each time integerB is updated. I am not sure if this is waht you want or not.

You should be able to tweak this to match the exact joins and logic you need.

Tip

Get your SQL statement working with literal values first and then translate it into your trigger. It will be much easier if you can get something working manually first before you attempt to make things more complicated

Table C is a copy of table A with two additional columns: integerB and sequence. The point of the trigger is to add new rows from table A without repetition, integerB from table B(integer in table B) and sequence should start with 1 and increment by 1. If the row in table A is repeated then only integerB should be updated. — H22, Jan 23 '23 at 18:05
Fixe typo but this is why you need better column names. It is still very unclear as to what you actually want but as I said above, try and get the SQL you want executed sorted and then work it into the trigger. I don't understand why you want `C` to be a copy of `A` but then have this update from a trigger on `B`. You need to explain the role `B` plays and how this joins to `A` (ie. if I insert a record in `B` how do I know which record in `C` I want to update? What happens if there is a record in `A` that isn't in `B`, should this still exist in `C` — Jameson_uk, Jan 24 '23 at 08:20

adding data from two different tables using a trigger

1 Answers1

Tip