SQL - Computed Columns / Triggers?

Question

I have created two tables:

In the first table I want to store a computed result in the 3rd column

Table 1

num1|num2|num3

I want to have num3 = num1+num2, after every update, insert, delete

My second table is for a percent calculation, something like a wishlist

Table 2 

value|stored|left

I want to have left = stored/value, ie. a percentage of the amount stored vs the value of an item.

How can I set these values up to compute on update, insert, delete?

Original:

i have created two tabels, in first one i try to store the result in the 3'rd column ex: my tabel structure num1|num2|num3

where num3=num1+num2 ,after every update,insert,delete

second tabel is for a percent calculation, something like a wishlist structure: value,stored,left

where value = the value of the object u wanna buy, ex 1250 stored is the ammount that you saved for your wish left is the percent left to achive your goal

same problem like the first tabel

Please tag RDBMS you are using, Also please add sample data in text format. what have you tried so far ? — Ven, Jan 22 '18 at 15:45
@TerchilăMarian I cleaned up your question, please check that the logic is correct. Also, tag your DBMS as House mentioned. — Aaron Dietz, Jan 22 '18 at 15:51
@TerchilăMarian I put your original question back in too. Edit it so it makes sense. — Aaron Dietz, Jan 22 '18 at 15:57
@TerchilăMarian This question still needs the appropriate tag to indicate which DBMS you're using. — , Jan 22 '18 at 16:12
Does this answer your question? [Column calculated from another column?](https://stackoverflow.com/questions/5222044/column-calculated-from-another-column) — phil294, Jun 09 '21 at 01:01

score 2 · Accepted Answer · answered Jan 22 '18 at 16:09

Creating a trigger will help update your table after insert or update on the table will help update column , assuming u have an ID column in the table

CREATE TRIGGER Insert_Update ON mytable
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF 
        UPDATE (num3)

    BEGIN
        UPDATE dbo.Mytable
        SET num3 = i.num1 + i.num2
        FROM inserted i
        INNER JOIN mytable t ON i.MyID = t.MyID
        LEFT JOIN deleted d ON i.MyID = d.MyID
        WHERE d.MyID IS NULL AND i.ColumnB IS NOT NULL -- Row was inserted
            OR d.MyID IS NOT NULL -- Row was updated
    END
END
GO

What is the `if UPDATE(num3)` doing? – Terry Jun 21 '20 at 19:13 — Terry, Jun 21 '20 at 19:13
Shouldn't this be `IF UPDATE(num1, num2)` ? – Hogan Nov 04 '22 at 20:14 — Hogan, Nov 04 '22 at 20:14

score 0 · Answer 2 · answered Jan 22 '18 at 15:54

0

It would be better if you store the num1 and num2 values in variables before setting num3. Unless you perform an INSERT first in the first two columns and then an UPDATE on the num3 column.

Sometimes a database isn't set for autocommit, so you'll need to force it and after that reuse the values.

answered Jan 22 '18 at 15:54

Mayko Gob

1
1

You probably won't do what you want in one single statement, because the values you'll need aren't on the table yet. Try to insert the first values, and then use a TRIGGER AFTER INSERT to UPDATE the NUM3. Be sure to set the trigger only for "after insert" otherwise you'll get a loop. BEGIN DECLARE num1_v numeric(15,2) DECLARE num2_v numeric(15,2) DECLARE num3_v numeric(15,2) SELECT num1_v = NUM1, num2_v = NUM2 FROM inserted (or deleted) WHERE = UPDATE set NUM3 = num1_v + num2_v WHERE = END
– Mayko Gob Jan 22 '18 at 16:18

SQL - Computed Columns / Triggers?

2 Answers2

Linked