get updated field name and value in trigger

Question

i have one table called product

product_id    rate      Quantity    modified_fields
    1          100         4
    3          32          2
    5          43          1

so if i am updating any field in product table

suppose i am changing rate 100 to 50 then i need to set up one trigger that will update modified_fields column with rate:50(New value)

 product_id       rate      Quantity    modified_fields
        1          50          4         rate:50
        3          32          2
        5          43          1

Edit: What I basically need is that I need to identify which particular column was edited on an Update operation. Table can have 50 columns, and any one of them can be updated. Trigger will basically determine which column is updated, and the corresponding new value. It will then update the modified fields column with the update column name : new value

Any help would be appreciated

you can directly write simple query as UPDATE product set rate = 50, modified_fields = CONCAT('rate',':',50) for same table, if it will be other table then trigger fine, but for same table you could use this — Rahul, Jan 10 '17 at 11:03
@VforVendettaWhat I basically need is that I need to identify which particular column was edited on an Update operation. Table can have 50 columns, and any one of them can be updated. Trigger will basically determine which column is updated, and the corresponding new value. It will then update the modified fields column with the update column name : new value — Ketan G, Jan 10 '17 at 12:15

score 1 · Answer 1 · edited May 23 '17 at 11:54

1

You can get the reference of link1 and link2.

Alternatively, you can try like this,

UPDATE product set rate = 50, modified_fields = CONCAT('rate',':',50);

If it is for same table. Trigger should be used in case of updating columns of another table.

edited May 23 '17 at 11:54

Community

1
1

answered Jan 10 '17 at 11:05

Rahul

18,271
7
41
60

we can't update product table after Update using trigger – Ketan G Jan 10 '17 at 11:18
once i used this before update trigger in the same table , it worked – Ketan G Jan 10 '17 at 11:19
Is it necessary to run trigger after update ? means can't we run trigger before update ? – Rahul Jan 10 '17 at 11:22
so we can solve this above mentioned problem using before update trigger ? if yes then how? – Ketan G Jan 10 '17 at 11:25
CREATE TRIGGER update_trig BEFORE UPDATE ON product FOR EACH ROW BEGIN SET new.modified_fields= CONCAT('rate',':',rate) END ; – Rahul Jan 10 '17 at 11:30
what if i am updating another field and how i will get updated value in trigger – Ketan G Jan 10 '17 at 11:33
i can't hard code trigger like this new.modified_fields= CONCAT('rate',':',rate) – Ketan G Jan 10 '17 at 11:34
Why don't you create log table to maintain each n every changes in the table, is it necessary to maintain in same table ? – Rahul Jan 10 '17 at 11:39
And meanwhile, you can create trigger for the same – Rahul Jan 10 '17 at 11:43
NO my requirements are different – Ketan G Jan 10 '17 at 11:44
You are using mysql with which technology ? – Rahul Jan 10 '17 at 11:45
php with mysql db – Ketan G Jan 10 '17 at 11:55
So you can append every changes to modified_fields with whatever delimeter you want, why trigger ? – Rahul Jan 10 '17 at 11:56
i want that automatically – Ketan G Jan 10 '17 at 11:58

get updated field name and value in trigger

1 Answers1