I'm wondering if an extra pair of eyes can find a problem with this trigger:
DELIMITER // CREATE TRIGGER `auto_approval` BEFORE UPDATE ON `my_table` FOR EACH ROW BEGIN SELECT security INTO user_sec FROM users WHERE userid=NEW.userid; IF user_sec >= 10 THEN set NEW.admin = 1; IF NEW.admin != 0 THEN SET NEW.approved = NOW(); END IF; END // DELIMITER ;
- The SELECT gets the users security level from the users table and stores it in a variable called user_sec
- The first IF checks user_sec to see if its a high enough level (greater than 10) and if so, it sets the approving admin to 1 (system default)
- The second IF says that if the NEW.admin !=0 then we'll set the approved date to the NOW().
Will this work? (I dont have a test server, so this gets run on the production server as is and I really wanna do my best to make sure it works!)