SQL: I need a column to update its value automatically based on other columns' values

Question

Suppose we have two tables:

Employees (id (pk), salary)
Bonus (id (fk), increase, total)

bonus.id is a FK that references the PK 'employees.id'.

I want total to be equal to employees.salary + bonus.increase by default. I also want the value to be assigned to total whenever salary or bonus are updated.

What sql or pl/sql statements are capable of doing this?

Note: I'm an absolute noob.

Consider not storing derived data at all. Get them with a view. — Serg, Jul 22 '19 at 08:38
Create `after update` trigger https://stackoverflow.com/questions/48385268/sql-computed-columns-triggers/48385800#48385800 — Ven, Jul 22 '19 at 08:45
@Tejash----ORA-00980 I'm looking up triggers right now. I think they will do the trick. I just have never heard of them before. Thanks! — Jim, Jul 22 '19 at 08:47
@Serg I can't use a view. I'm just working on an assignment. — Jim, Jul 22 '19 at 08:48
In a normal company structure every employee has a salary. However, bonuses are awarded on an ad hoc basis, with no employee guaranteed a bonus, and the bonus awarded might be different each year. Is that your model? When an employee is created do they have a bonus record? Does a new bonus mean a new record or an update to the existing record? — APC, Jul 22 '19 at 09:10
You could use the triggers to achieve that but that would be a very bad choice. As a matter of fact you probably don't require the total column at all. You could have that column in a view as mentioned by @Thumbquat. — Yawer Ubaid, Jul 22 '19 at 08:46
Thanks! I think I have to use a trigger. I know it's not a good choice and the tables don't really make sense, but I'm only doing this for educational purposes — Jim, Jul 22 '19 at 08:52
This is not an answer. It should be a comment - indeed you are agreeing with posted comments - but obviously you don't have sufficient reputation points to post comments yet. Alas, posting non-answers like this should not be the way to gain points — APC, Jul 22 '19 at 09:05
You should expand this so that it becomes an answer. How would you solve it with a trigger (provide a working example based on the OP's tables)? Expand on why this is a bad idea. Give an example of how a better solution than a trigger would be implemented. — MT0, Jul 22 '19 at 09:07

score 2 · Answer 1 · answered Jul 22 '19 at 09:22

If there is always a one-to-one correspondence between the rows of the bonus table and the employees table then the better solution would be to push back that the requirements are wrong and the bonus table should be rolled into the employee table and the total column could be a VIRTUAL column:

CREATE TABLE employees (
  id     NUMBER PRIMARY KEY,
  salary NUMBER,
  bonus  NUMBER,
  total  NUMBER GENERATED ALWAYS AS ( salary + COALESCE( bonus, 0 ) )
);

INSERT INTO employees ( id, salary, bonus )
  SELECT 1, 100, 10   FROM DUAL UNION ALL
  SELECT 2, 100, NULL FROM DUAL;

Then:

SELECT * FROM employees;

Outputs:

ID | SALARY | BONUS | TOTAL
-: | -----: | ----: | ----:
 1 |    100 |    10 |   110
 2 |    100 |  null |   100

db<>fiddle here

Your answer was very helpful! But I can't change the structures. So, I used triggers instead. I highly appreciate your effort! Thanks! — Jim, Jul 22 '19 at 10:22

score 1 · Accepted Answer · answered Jul 22 '19 at 09:39

The previous answer by @MT0 is perfect.

But if you can not change the structure of the table and need Trigger solution then you need the following two triggers.

CREATE OR REPLACE TRIGGER TRG_EMPLOYEES AFTER
    UPDATE OF SALARY ON EMPLOYEES
    FOR EACH ROW
BEGIN
    UPDATE BONUS
    SET
        TOTAL = :NEW.SALARY + INCREASE
    WHERE
        ID = :NEW.ID;

END;
/


CREATE OR REPLACE TRIGGER TRG_BONUS BEFORE
    INSERT OR UPDATE OF INCREASE ON BONUS
    FOR EACH ROW
DECLARE
    LV_TOTAL   BONUS.TOTAL%TYPE;
BEGIN
    SELECT
        SALARY + NVL(:NEW.INCREASE, 0)
    INTO LV_TOTAL
    FROM
        EMPLOYEES
    WHERE
        ID = :NEW.ID;

    :NEW.TOTAL := LV_TOTAL;
END;
/

db<>fiddle demo

Cheers!!

score 0 · Answer 3 · edited Jul 22 '19 at 09:47

0

You could do this easily with a view:

CREATE VIEW EMPLOYEE_CALC_TOTAL AS
SELECT e.id, e.salary + b.increase as total
  FROM employees e
  JOIN bonus b ON b.id = e.id

This would stay up to date automatically. However if employee to bonus isn't always a 1 to 1 relationship you'd get duplicate rows for each extra bonus row.

edited Jul 22 '19 at 09:47

MartenCatcher

2,713
8
26
39

answered Jul 22 '19 at 08:41

Thumbquat

56
3

Thanks! but that doesn't update the bonus table. I need it to be updated. – Jim Jul 22 '19 at 08:45
1

You can create a trigger when insert and update to get these values and update your total column – Shehab Jul 22 '19 at 08:47

SQL: I need a column to update its value automatically based on other columns' values

3 Answers3