CODEWITHSUNDEEP
oracle-databasetriggersapex
user111
user111

Reputation: 137

Create trigger that updates row depending on column value from other table

I would like to create a trigger that updates a row in a table (TABLE2) depending on column value from other table (TABLE1). Which line must be updated depends on the ID of TBL1.

    CREATE OR REPLACE TRIGGER INSERT_PAGE 
    BEFORE UPDATE OR INSERT
    ON TABLE1 
    FOR EACH ROW
    BEGIN
        IF INSERTING THEN 
                INSERT INTO TABLE2 (TBL1ID,STEP,PAGE) VALUES 
                (:NEW.TBL1ID,:NEW.STEP,15); 
        ELSIF UPDATING and :NEW.STATE='APPROVED' THEN 
            UPDATE TABLE2 
            SET PAGE=16 AND STEP1='TEXT123' 
            WHERE TBL1ID =TABLE1.TBL1ID; 
        END IF; 
    END;

Can someone help me in creating a trigger with an update statement? Would this be as well a good way to go?

So I have two tables, the column state in table1 is changing throughtout the process. Depending on the change of the column state from table1, I want to change or better say update the row in table 2 and set some columns like PAGE and STATE in Table 2 depending on column value STATE in Table 1. I do not want that a new row being created each time TABLE 1 gets updated, only the corresponding row should be updated.

Table 1

Table 2

Upvotes: 0

Views: 1435

Answers (1)

Aman Singh Rajpoot
Aman Singh Rajpoot

Reputation: 1479

As far as I understood is you want to update table2 only when the state in table1 changed to 'approved' for a row and if a row is inserted in table1 trigger will insert the row in table2.

I have made some corrections to your code. Let me know if it is not what you wanted.

CREATE OR REPLACE TRIGGER INSERT_PAGE 
    BEFORE UPDATE OR INSERT
    ON TABLE1 
    FOR EACH ROW
    DECLARE 
    BEGIN
        IF INSERTING THEN 
                INSERT INTO TABLE2 (TBL1ID,STEP,PAGE) VALUES 
                (:NEW.TBL1ID,:NEW.STEP,15); 
      
        ELSIF UPDATING THEN 
          
            IF :NEW.STATE = 'APPROVED' THEN 
          
               UPDATE table2 t2 SET
               STATE = :NEW.STATE, PAGE=16, STEP1='TEXT123'      
               WHERE t2.TBL1ID = :OLD.TBL1ID;
            
            END IF;
          
        END IF; 
    END;

Upvotes: 1

Related Questions