simple triger

Discussion in 'Databases' started by bumbar, Nov 27, 2010.

0
  1. #1
    Hallo!

    I have table 

    CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `current` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
)
    PHP:
    and triger

    delimiter |
CREATE TRIGGER myTriger BEFORE INSERT ON users
  FOR EACH ROW BEGIN
    UPDATE users SET current = 0 WHERE current=1; 
  END;
|
delimiter ;
    PHP:
    but try to enter a new user I get an error:
    "Can't update table 'users' in stored function/trigger because it is already used by statement which invoked this stored function/trigger"

    purpose of the trigger is reset the values 1 of a current table to 0 (null)

    Thank you!
     
    bumbar, Nov 27, 2010 IP
  2. iama_gamer

    iama_gamer Active Member

    Messages:
    404
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    60
    #2
    MySql has a recursion in this way. Try to make a use of the NEW object.

    During the insert/update you have access to the NEW object which contains all of the fields in the table involved. If you do a before insert/update and edit the field that you want to change in the new object it will become a part of the calling statement and not be executed as a separately (eliminating the recursion)
     
    iama_gamer, Dec 4, 2010 IP