PL/SQL Using the :New & :old system with conditionals from different tables in DDL

Question

Hello I have a correlative doubt to the triggers, and the DDL aspect, when you made an update of an event and this event depends on two tables, you can make a conditional.

EDIT:

The questions are: -Can a different table be used for the condition of a table update? -Can a :NEW value from another table be used in a table? -Can I use an :OLD value from another table? -Can the value of a :NEW column from another table be used as an update condition? -Can I use the condition: tablename.columname = :NEW.tablename.columname ?

The issue is the following:

Create or replace trigger TGR_example_consumer
AFTER INSERT OR UPDATE OR DELETE ON CONSUMER
FOR EACH ROW
BEGIN
     IF INSERTING THEN
        UPDATE consumption
            SET amount = amount + :NEW.amount
        WHERE ID_consumption = :NEW.ID_consumption;
        UPDATE TOTAL_consumptions
            SET amount_consumption = amount_consumption + 
               :OLD.consumption.amount;
        WHERE consumption.ID_consumption = :NEW.consumption.ID_consumption; -- > In Here is my dude.       
    ELSIF UPDATING THEN
        UPDATE consumption
            SET amount= (amount+ :OLD.amount) - :NEW.amount
        WHERE ID_consumption = :NEW.ID_consumption;
    ELSE
        UPDATE consumption
            SET amount= amount+ OLD.amount
        WHERE ID_consumption = :NEW.ID_consumption;
    END IF;
END;

It seems to me that mine is very punctual that's why I opened the thread. Because I haven't seen anything similar.

EDIT: I hope I have clarified the issue, what I want to do is under the update of another table in a trigger, update the table automatically, almost like an inventory or stock control.

I am trying to work out what the question is asking, or even whether you have asked a question. Please [edit] your post to clarify what the problem is. — MT0, Jul 02 '22 at 20:25
As for the `Update ... From` question: https://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table — Stefan Wuebbe, Jul 03 '22 at 12:14

score 0 · Answer 1 · answered Jul 03 '22 at 01:26

The trigger you show looks like it should run fine, except for the fact that in the DELETE case all of the :NEW fields are NULL. You'll have to use the :OLD field. Also, you have OLD.amount in the fourth-from-last line - that should be :OLD.amount. Also, get rid of the semi-colon at the end of line 11.

Create or replace trigger TGR_example_consumer
AFTER INSERT OR UPDATE OR DELETE ON CONSUMER
FOR EACH ROW
BEGIN
     IF INSERTING THEN
        UPDATE consumption
            SET amount = amount + :NEW.amount
        WHERE ID_consumption = :NEW.ID_consumption;
        UPDATE TOTAL_consumptions
            SET amount_consumption = amount_consumption + 
               :OLD.consumption.amount
        WHERE consumption.ID_consumption = :NEW.consumption.ID_consumption;
    ELSIF UPDATING THEN
        UPDATE consumption
            SET amount= (amount+ :OLD.amount) - :NEW.amount
        WHERE ID_consumption = :NEW.ID_consumption;
    ELSE
        UPDATE consumption
            SET amount= amount+ :OLD.amount
        WHERE ID_consumption = :OLD.ID_consumption;
    END IF;
END;

PL/SQL Using the :New & :old system with conditionals from different tables in DDL

1 Answers1