I need a trigger review...

[ppetree]

Hi All...

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 ;

1. The SELECT gets the users security level from the users table and stores it in a variable called user_sec
2. 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)
3. 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!)

Thanks

Pete

[adam_k]

select security into

will result in a table with a column named security. Unfortunately this will only run once, as the second time around the table will exist and you'll receive an error.
If you intend user_sec to be a variable, you need to declare it and use the @ sign in it's name. To populate your variable use the output instead of the into.

PS: If you don't have a test server, this doesn't mean that you can't have a test instance or test database or even test tables. Create a copy of your table and try out the trigger. The forum can help, but you have the responsibility, so make sure you are ok by testing.

[ppetree]

Adam,

Thanks for your reply... obviously I have no idea how to use variables in triggers so how do you use the @?

Thanks,

Pete

[ppetree]

I got it working... final code was this:

DELIMITER //
CREATE TRIGGER trigger_name BEFORE INSERT ON my_table
FOR EACH ROW BEGIN
  declare user_sec tinyint(1);

  SELECT security INTO user_sec FROM users WHERE user_id = NEW.user_id;

  IF user_sec >= 10 AND NEW.admin = 0 THEN
    SET NEW.admin = 1;
    SET NEW.approved = NOW();
  END IF;
END //
DELIMITER ;