Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

SOLVED The users table:

CREATE TABLE `users` (
  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(45) DEFAULT NULL,
  `username` varchar(16) DEFAULT NULL,
  `salt` varchar(16) DEFAULT NULL,
  `password` varchar(128) DEFAULT NULL,
  `lastlogin` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `joined` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `loggedin` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `sessionkey` varchar(60) DEFAULT NULL,
  `verifycode` varchar(16) DEFAULT NULL,
  `verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `banned` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `locked` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `ip_address` varchar(45) DEFAULT NULL,
  `failedattempts` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `unlocktime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

The user_records table:

CREATE TABLE `user_records` (
  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `userid` int(8) unsigned DEFAULT NULL,
  `action` varchar(100) DEFAULT NULL,
  `oldentry` varchar(100) DEFAULT NULL,
  `newentry` varchar(100) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

My trigger code for the users table to insert on user_records table:

    USE `gknet`;
   DELIMITER $$
   CREATE TRIGGER `after_update_user` AFTER UPDATE ON `users` FOR EACH ROW BEGIN
    IF NEW.loggedin != OLD.loggedin OR NEW.sessionkey != OLD.sessionkey THEN
        IF NEW.loggedin = '1' THEN
            INSERT INTO users (NEW.lastlogin)
            VALUES (NOW());
            INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
            VALUES (NEW.ID,'LOGGED_IN',OLD.sessionkey,NEW.sessionkey,NOW());
        ELSE IF NEW.loggedin = '0' THEN
            INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
            VALUES (NEW.ID,'LOGGED_OUT',OLD.sessionkey,NEW.sessionkey,NOW());
        END IF;
    END IF;

    IF NEW.locked != OLD.locked OR NEW.unlocktime != OLD.unlocktime THEN
        IF NEW.locked = '1' THEN
            INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
            VALUES (NEW.ID,'LOCKED',OLD.unlocktime,NEW.unlocktime,NOW());
        ELSE IF NEW.locked = '0' THEN
            INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
            VALUES (NEW.ID,'UNLOCKED',OLD.unlocktime,NEW.unlocktime,NOW());
        END IF;
    END IF;

    IF NEW.ip_address != OLD.ip_address THEN
        INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
        VALUES (NEW.ID,'IP_CHANGE',OLD.ip_address,NEW.ip_address,NOW());
    END IF;

    IF NEW.verified != OLD.verified THEN
        IF NEW.verified = '1' THEN
            INSERT INTO user_records (userid, action, timestamp)
            VALUES (NEW.ID,'VERIFIED',NOW());
        ELSE IF NEW.verified = '0' THEN
            INSERT INTO user_records (userid, action, timestamp)
            VALUES (NEW.ID,'UNVERIFIED',NOW());
        END IF;
    END IF;
END
$$

MySQL keeps saying there is an error near line 38 (second END IF from the bottom). If I remove that whole IF statement, it will give the error again for the one above it. How do I fix this? What is the correct syntax?

Edit: Replacing ELSE IF with ELSEIF like so doesn't help:

USE `gknet`;
DELIMITER $$
CREATE TRIGGER `after_update_user` AFTER UPDATE ON `users` FOR EACH ROW BEGIN
IF NEW.loggedin != OLD.loggedin OR NEW.sessionkey != OLD.sessionkey THEN
    IF NEW.loggedin = '1' THEN
        INSERT INTO users (NEW.lastlogin)
        VALUES (NOW());
        INSERT INTO user_records (userid, action, oldentry, newentry,  timestamp)
        VALUES (NEW.ID,'LOGGED_IN',OLD.sessionkey,NEW.sessionkey,NOW());
    ELSEIF NEW.loggedin = '0' THEN
        INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
        VALUES (NEW.ID,'LOGGED_OUT',OLD.sessionkey,NEW.sessionkey,NOW());
    END IF;
END IF;

IF NEW.locked != OLD.locked OR NEW.unlocktime != OLD.unlocktime THEN
    IF NEW.locked = '1' THEN
        INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
        VALUES (NEW.ID,'LOCKED',OLD.unlocktime,NEW.unlocktime,NOW());
    ELSEIF NEW.locked = '0' THEN
        INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
        VALUES (NEW.ID,'UNLOCKED',OLD.unlocktime,NEW.unlocktime,NOW());
    END IF;
END IF;

IF NEW.ip_address != OLD.ip_address THEN
    INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
    VALUES (NEW.ID,'IP_CHANGE',OLD.ip_address,NEW.ip_address,NOW());
END IF;

IF NEW.verified != OLD.verified THEN
    IF NEW.verified = '1' THEN
        INSERT INTO user_records (userid, action, timestamp)
        VALUES (NEW.ID,'VERIFIED',NOW());
    ELSEIF NEW.verified = '0' THEN
        INSERT INTO user_records (userid, action, timestamp)
        VALUES (NEW.ID,'UNVERIFIED',NOW());
    END IF;
END IF;
END;
$$

Edit: Just removed the line INSERTing into the users table. Same error

USE `gknet`;
DELIMITER $$
CREATE TRIGGER `after_update_user` AFTER UPDATE ON `users` FOR EACH ROW BEGIN
IF NEW.loggedin != OLD.loggedin OR NEW.sessionkey != OLD.sessionkey THEN
    IF NEW.loggedin = '1' THEN
        INSERT INTO user_records (userid, action, oldentry, newentry,  timestamp)
        VALUES (NEW.ID,'LOGGED_IN',OLD.sessionkey,NEW.sessionkey,NOW());
    ELSEIF NEW.loggedin = '0' THEN
        INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
        VALUES (NEW.ID,'LOGGED_OUT',OLD.sessionkey,NEW.sessionkey,NOW());
    END IF;
END IF;

IF NEW.locked != OLD.locked OR NEW.unlocktime != OLD.unlocktime THEN
    IF NEW.locked = '1' THEN
        INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
        VALUES (NEW.ID,'LOCKED',OLD.unlocktime,NEW.unlocktime,NOW());
    ELSEIF NEW.locked = '0' THEN
        INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
        VALUES (NEW.ID,'UNLOCKED',OLD.unlocktime,NEW.unlocktime,NOW());
    END IF;
END IF;

IF NEW.ip_address != OLD.ip_address THEN
    INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
    VALUES (NEW.ID,'IP_CHANGE',OLD.ip_address,NEW.ip_address,NOW());
END IF;

IF NEW.verified != OLD.verified THEN
    IF NEW.verified = '1' THEN
        INSERT INTO user_records (userid, action, timestamp)
        VALUES (NEW.ID,'VERIFIED',NOW());
    ELSEIF NEW.verified = '0' THEN
        INSERT INTO user_records (userid, action, timestamp)
        VALUES (NEW.ID,'UNVERIFIED',NOW());
    END IF;
END IF;
END;
$$
share|improve this question

2 Answers 2

up vote 0 down vote accepted

I indented everything and quickly noticed that the last END must have $$ after it, not the ;

USE `gknet`;
DELIMITER $$
CREATE TRIGGER `after_update_user` AFTER UPDATE ON `users` FOR EACH ROW
BEGIN
    IF NEW.loggedin != OLD.loggedin OR NEW.sessionkey != OLD.sessionkey THEN
        IF NEW.loggedin = '1' THEN
            INSERT INTO user_records (userid, action, oldentry, newentry,  timestamp)
            VALUES (NEW.ID,'LOGGED_IN',OLD.sessionkey,NEW.sessionkey,NOW());
        ELSEIF NEW.loggedin = '0' THEN
            INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
            VALUES (NEW.ID,'LOGGED_OUT',OLD.sessionkey,NEW.sessionkey,NOW());
        END IF;
    END IF;
    IF NEW.locked != OLD.locked OR NEW.unlocktime != OLD.unlocktime THEN
        IF NEW.locked = '1' THEN
            INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
            VALUES (NEW.ID,'LOCKED',OLD.unlocktime,NEW.unlocktime,NOW());
        ELSEIF NEW.locked = '0' THEN
            INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
            VALUES (NEW.ID,'UNLOCKED',OLD.unlocktime,NEW.unlocktime,NOW());
        END IF;
    END IF;
    IF NEW.ip_address != OLD.ip_address THEN
        INSERT INTO user_records (userid, action, oldentry, newentry, timestamp)
        VALUES (NEW.ID,'IP_CHANGE',OLD.ip_address,NEW.ip_address,NOW());
    END IF;
    IF NEW.verified != OLD.verified THEN
        IF NEW.verified = '1' THEN
            INSERT INTO user_records (userid, action, timestamp)
            VALUES (NEW.ID,'VERIFIED',NOW());
        ELSEIF NEW.verified = '0' THEN
            INSERT INTO user_records (userid, action, timestamp)
            VALUES (NEW.ID,'UNVERIFIED',NOW());
        END IF;
    END IF;
END $$
DELIMITER ;

I also appended

DELIMITER ;

to get the ; back as the normal command delimiter.

share|improve this answer
    
That worked, thanks! Sorry, I can't upvote! Rep is too low –  user3748334 Jun 27 at 1:08
    
If you want to, please click on the checkmark next to this answer to mark the answer accepted. –  RolandoMySQLDBA Jun 27 at 1:10

Try this (killed me for 1/2 an hour... Doh...).

Note the use of ELSEIF (and NOT ELSE IF... compiler should have picked that up! :-) ) - only did 1 - should work for the others.

DELIMITER $$
CREATE TRIGGER `after_update_user` AFTER UPDATE ON `users` 
FOR EACH ROW 
BEGIN
  IF NEW.loggedin != OLD.loggedin OR NEW.sessionkey != OLD.sessionkey THEN
    IF NEW.loggedin = '1' THEN
      BEGIN
        INSERT INTO users (NEW.lastlogin) VALUES (NOW());
        INSERT INTO user_records (userid, action, oldentry, newentry, timestamp) VALUES (NEW.ID, 'LOGGED_IN' , OLD.sessionkey, NEW.sessionkey, NOW());
      END;
    ELSEIF NEW.loggedin = '0' THEN
      BEGIN
        INSERT INTO user_records (userid, action, oldentry, newentry, timestamp) VALUES (NEW.ID, 'LOGGED_OUT', OLD.sessionkey, NEW.sessionkey, NOW());
      END;
    END IF;
  END IF;
END; $$
DELIMITER ;

p.s. the BEGIN and END after the multiple statements inside the IF loop isn't strictly necessary, but it's clearer (I think).

share|improve this answer
    
After replacing ELSE IF with ELSEIF, it still gives the same error –  user3748334 Jun 27 at 0:07
    
OK - I'm now recompiling MySQL from source - what, exactly, was your error? –  Vérace Jun 27 at 0:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.