Oracle trigger with connection to different tables

Question

I want to make trigger, which counts price from different tables.

It looks like this:

PART_PRICE(from PARTS) + TIME(from PARTS) * SALARY_PER_HOUR(from MECHANIC)

and the result should be saved in PRICE (from RESERVATIONS), the table RESERVATIONS contains ID_PART and ID_MECHANIC connected to PARTS & MECHANIC.

I really don't know how to do it, can you help me?

[Do not put business logic in triggers](http://stackoverflow.com/questions/34362598/trigger-cant-read-the-table-after-being-fired-by-the-same-table/34371316#34371316). No, seriously,[I mean it - [DO NOT PUT BUSINESS LOGIC IN TRIGGERS](http://stackoverflow.com/questions/31998789/trigger-to-enforce-m-m-relationship/32009988#32009988)! — Bob Jarvis - Слава Україні, May 22 '16 at 16:10

score 0 · Answer 1 · answered May 23 '16 at 12:42

First of all trigger should be fired on DDL statements. It is not clear from the question that on which table changes you want to trigger. As per your questions I think you want to update price column of Reservations table, if there is any changes in ID_PART or ID_MECHANIC. Please find the code below.

CREATE TRIGGER trgAfterUpdate AFTER UPDATE ON RESERVATIONS 
FOR EACH ROW
AS
    declare part_id int;
    declare mechanic_id int;
    declare time long;
    declare part_price decimal(10,2);
    declare mechanic_sal decimal(10,2); 

    if NEW.ID_PART <> OLD.ID_PART 
        part_price = select PART_PRICE from parts where id_part=new.id_part;
        time = select TIME from parts where id_part=new.id_part;
        mechanic_sal = select SALARY_PER_HOUR from mechanic where id_mechanic=old.id_mechanic;
        update RESERVATIONS set PRICE = part_price+time*mechanic_sal where ID_PART=NEW.ID_PART and ID_MECHANIC=OLD.id_mechanic;
    else if NEW.ID_MECHANIC <> OLD.ID_MECHANIC
        part_price = select PART_PRICE from parts where id_part=old.id_part;
        time = select TIME from parts where id_part=old.id_part;
        mechanic_sal = select SALARY_PER_HOUR from mechanic where id_mechanic=new.id_mechanic;
        update RESERVATIONS set PRICE = part_price+time*mechanic_sal where ID_PART=OLD.ID_PART and ID_MECHANIC=NEW.id_mechanic;
GO

Oracle trigger with connection to different tables

1 Answers1