CODEWITHSUNDEEP
phpmysql
Weacked
Weacked

Reputation: 970

Change a value when a value get updated

I know there's this statement ON UPDATE, is there a way to use it to create a column in my database and when this value change, another predefined value is set to 0 ? Thanks in advance.

Exemple: 

id | value | type
1      2      5
2      4      6
3      7      8

if i update the value of the first row, i want the type to set automaticaly to 0.

Upvotes: 2

Views: 387

Answers (4)

ariefbayu
ariefbayu

Reputation: 21979

You need to create a trigger that gets executed for every time row changed, for example:

CREATE TRIGGER auto_update_type BEFORE UPDATE
ON your_table_name
FOR EACH ROW BEGIN

    if NEW.value <> OLD.value THEN
        SET NEW.type = 0;
    END IF;
END;

After this, for everytime you do query:

UPDATE table_name SET value = '10' WHERE id = 1;

If the value is different that the old one, trigger will gets executed and type will be set to 0

Here's how to set trigger from phpMyAdmin:

enter image description here

Upvotes: 2

Sergey Karasev
Sergey Karasev

Reputation: 4843

need use mysql trigger for this row

Upvotes: 0

Ed Heal
Ed Heal

Reputation: 60037

Remove all access to insert, update and delete SQL statements from your PHP script. Just use stored procedures instead. That way the database can look after itself and maintain its own consistency.

Upvotes: 0

Suleman Ahmad
Suleman Ahmad

Reputation: 2113

One solution, You can do this just with the help of php code.

first of all whenever your update function/action is triggered then also update the type value as well.

Upvotes: 0

Related Questions