i want a trigger to be able to change the value of status=1 when balance is 0 and status=0 when the value of balance is not equal to 0 after update only ..
3
-
Here you find something on How to Ask and how to build a minimal reproducible exampleAleksej– Aleksej2016-08-19 11:42:37 +00:00Commented Aug 19, 2016 at 11:42
-
When do you want to fire a trigger ? After insert ??Priyanshu– Priyanshu2016-08-19 11:48:43 +00:00Commented Aug 19, 2016 at 11:48
-
@priyanshu after updateiTech– iTech2016-08-19 11:55:11 +00:00Commented Aug 19, 2016 at 11:55
Add a comment
|
2 Answers
Use new row to both access and change values before the update is actioned.
create trigger mytrigger
before update on mytable
for each row
set new.status = (new.balance = 0);
Conveniently (in MySQL), true is 1 and false is 0.
Comments
CREATE TRIGGER `db`.`trigger_name`
AFTER INSERT ON `table_name` FOR EACH ROW
BEGIN
update table
set status = case when new.balance = 0 then '1' else 0 end;
It will check value of new balance value during INSERT and update the status. I hope this helps.
CREATE TRIGGER `db`.`trigger_name`
AFTER UPDATE ON `table_name` FOR EACH ROW
BEGIN
update table
set status = case when balance = 0 then '1' else 0 end;
1 Comment
Priyanshu
see my answer, It will check whole table and update.
