A trigger in Oracle SQL-CodePudding

I am trying to create a trigger that after INSERT OR UPDATE OF columnX OR DELETE ON tableA will increment or decrement by 1 columnY in tableB where tableB.key = tableA.key

For example: table product(droduct_id,product_rating) and table sale_line(sale_id, product_id,sale_qty) Every time a greater quantity is entered into sale_line.sale_qty, product.product_rating is incremented by 1 and vice versa. Thus, the greater is the quantity of a product in the sale_qty in the sale_line table, the greater is its rating in the product table

I have tried few variants found in different posts but none of them worked. If I manage incrementing o decrementing the rating column, it goes for the whole table and not for the specific row. How can I specify that changes in one table trigger changes in another table and all that happens for a certain product? Now I know that the problem is my insufficient knowledge and some guys expressed the opinion that a trigger is the last thing to have, but this is why I want to understand it. For the moment, this is what I have and it throws this error:

ORA-04079: invalid trigger specification

CREATE OR REPLACE TRIGGER t
  AFTER INSERT OR UPDATE  OF columnX OR DELETE
  ON tableA
    BEGIN
    IF INSERTING AND :NEW.columnX > 2 THEN
    UPDATE tableB 
    SET columnY = columnY   1 
    WHERE  columnY = 0;
    ELSIF UPDATING AND :NEW.columnX < :OLD.columnX THEN
    UPDATE tableB 
    SET columnY = columnY - 1 
    WHERE  columnY <> 0;
    ELSIF DELETING THEN
    UPDATE tableB 
    SET columnY = 0 
    WHERE  columnY <> 0;
    END IF;
    END;

CodePudding user response：

Try it like this:

create or replace TRIGGER trg_aft_ins_upd_del_a_tbl
    AFTER INSERT OR UPDATE OF COL_X OR DELETE 
    ON A_TBL 
    FOR EACH ROW
        DECLARE
            cnt   NUMBER;
        BEGIN
        IF INSERTING AND :new.COL_X > 2 THEN
              -- update if exist a row in B_TBL with ID = inserted ID - else insert new row in B_TBL
              Select Count(*) INTO cnt From B_TBL Where ID = :new.ID;
              IF cnt > 0 THEN
                UPDATE B_TBL 
                SET COL_Y = COL_Y   1 
                WHERE  ID = :new.ID;
              ELSE
                INSERT INTO B_TBL (ID, COL_Y) VALUES(:new.ID, 1);
              END IF;
              -- if update decreased the value
        ELSIF UPDATING AND :new.COL_X < :old.COL_X THEN
              UPDATE B_TBL 
              SET COL_Y =  COL_Y - 1 
              WHERE  ID = :new.ID;
              -- if update increased the value
        ELSIF UPDATING AND :new.COL_X > :old.COL_X THEN
              UPDATE B_TBL 
              SET COL_Y = COL_Y   1 
              WHERE  ID = :new.ID;
              -- on delete set to 0
        ELSIF DELETING THEN
              UPDATE B_TBL 
              SET COL_Y = 0 
              WHERE  ID = :old.ID;
        END IF;
END;