CODEWITHSUNDEEP
plsqloracle11g
Vic
Vic

Reputation: 118

Oracle to trigger an update upon insertion in another table

i would like to have a situation whereby i have 2 tables, table 1 is customers table with salary column and table 2 is a tax table. When i insert a record in the tax table with tax amount, i would like the amount to subtract the salary and the customer table (salary column) is update with the net salary.

 CREATE OR REPLACE TRIGGER trig_update
 AFTER INSERT ON tax 
 FOR EACH ROW
 DECLARE 
 net_sal;
 BEGIN
 net_sal := :customers.salary - :tax.amount;
 UPDATE customers (salary) VALUES (net_sal)
 WHERE (tax.cust_id == customers.id);
 END;

I am getting a compilation error

Upvotes: 0

Views: 50

Answers (1)

Kyle H
Kyle H

Reputation: 3293

First, there is no "==" operator in SQL. Only "=".

Second, you need a SET clause in your update statement. It should be

UPDATE customers
SET salary = net_sal
WHERE tax.cust_id = customers.id;

Third, this is bad table design. Instead of updating the salary column, make a new column called "net_salary" and update that column instead. What if you had to delete a tax record and insert a new one? Your salary value would still have the old taxes taken from it, being incorrectly low.

Upvotes: 1

Related Questions