CODEWITHSUNDEEP
sqloracle-databasejoinsql-insertdatabase-trigger
Johnniekf
Johnniekf

Reputation: 67

Trigger with insert and calculations

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.

Upvotes: 2

Views: 110

Answers (1)

GMB
GMB

Reputation: 222512

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.

Upvotes: 3

Related Questions