0

I would like to update processed value when the update happens on a row. If the processed <> 0 then update processed=1 else processed=0

Syntax error...

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '//' at line 1

My code:

CREATE TRIGGER users_after_update
AFTER update ON users 
FOR EACH ROW
BEGIN
 IF NEW.processed <> 0 THEN
  UPDATE users SET new.processed=1;
 ELSE 
  UPDATE users SET new.processed=0;
 END IF;
END;
Novák Róbert
  • 27
  • 6
  • Tried without delimiters? BTW do you really want to UPDATE ALL processed records? – P.Salmon Jan 18 '22 at 09:37
  • I just want to modify the newly update column's processed value. Code edited. – Novák Róbert Jan 18 '22 at 09:41
  • 1
    Sorry misread that - you cannot alter NEW. values after update and you cannot action the table which fired the trigger. Perhaps a before trigger might be what you want and it would help if you could provide sample data and expected outcome as text – P.Salmon Jan 18 '22 at 09:45
  • Example: Table First name,last name, city, processed If an update happens on a row and processed is not 0 i would like to setup it to 1. – Novák Róbert Jan 18 '22 at 09:48
  • CREATE TRIGGER users_after_update BEFORE UPDATE ON users FOR EACH ROW SET NEW.processed=1 This one works... CREATE TRIGGER users_after_update BEFORE UPDATE ON users FOR EACH ROW IF NEW.Processed <>0 THEN SET NEW.processed=1 END IF; This one doesn't work... – Novák Róbert Jan 18 '22 at 10:34
  • Any particular reason for not sharing the exact error message you got from your last try? I really don't like guessing... – Shadow Jan 18 '22 at 12:49
  • Your first trigger in your last comment contains a single statement and does not need delimiters or begin..end Your second trigger in the last comment contains more than one statement and DOES NEED delimiters and begin..end. Also I would check if any column is updating before setting processed – P.Salmon Jan 19 '22 at 08:25

0 Answers0