Trigger with insert and calculations

user_10284140 0
Viewed 37

I Have two tables, TableA which has a primary key (A_ID) and a salary column. Table B has a primary key (B_ID) and a paymentAmount column.

I need to create a trigger so that once TableB B_ID is inserted the trigger will go to TableA, find A_ID which matches B_ID, grab the salary on the relating column then divide it by 12 and finally add the result of that calculation to TableB paymentAmount column.

Here is my attempt but it does not compile;

CREATE TRIGGER test AFTER INSERT ON TableB
FOR EACH ROW
BEGIN
UPDATE TableB
SET TableB.paymentamount = TableA.salary / 12 WHERE TableA.staffid = TableB.staffid
END;

I've never used triggers before so apologies if this I'm going about this the wrong way.

database-trigger sql-insert join oracle sql
1 Answers

I think that this does what you want:

create trigger test before insert on tableb
for each row
declare
    v_salary number;
begin
    select salary into v_salary from tablea a where a.a_id= :new.b_id;
    :new.paymentamount := v_salary / 12;
end;
/

In a nutshell: this is a before trigger that modifies the paymentamount in the row that is about to be inserted. To get the relevant value, we query tablea for the row whose a_id matches the newly inserted b_id and recover the corresponding salary, that we assign to a variable.

user_106767160
Related