1

How would you create a trigger that uses the values of the row being inserted to be calculated first so that a value being inserted gets transformed?

Let's say I have this table labor_rates,

+---------------+-----------------+--------------+------------+
| labor_rate_id | rate_per_minute | unit_minutes | created_at |
+---------------+-----------------+--------------+------------+
| bigint        | numeric         | numeric      | timestamp  |
+---------------+-----------------+--------------+------------+

Each time a new record is created, I need that the rate is calculated as rate/unit (the smallest unit here is a minute).

So example, when inserting a new record:

INSERT INTO labor_rates(rate, unit)
VALUES (60, 480);

It would create a new record with these values:

+---------------+-----------------+--------------+----------------------------+
| labor_rate_id | rate_per_minute | unit_minutes |         created_at         |
+---------------+-----------------+--------------+----------------------------+
|       1000000 |          1.1979 |           60 | 2017-03-16 01:59:47.208111 |
+---------------+-----------------+--------------+----------------------------+

One could argue that this should be left as a calculated field instead of storing the calculated value. But in this case, it would be best if the calculated value is stored.

I am fairly new to triggers so any help would be much appreciated.

svajone
  • 69
  • 2
  • 10
  • 1
    Possible duplicate of [Computed / calculated columns in PostgreSQL](http://stackoverflow.com/questions/8250389/computed-calculated-columns-in-postgresql) – krokodilko Mar 18 '17 at 17:29

0 Answers0