Error in mysql Trigger

I am trying to create a trigger. First of all I created the table employees and the table employees_audit:

CREATE TABLE employees (
  employeeNumber int(11) NOT NULL,
  lastName varchar(50) NOT NULL,
  firstName varchar(50) NOT NULL,
  extension varchar(10) NOT NULL,
  email varchar(100) NOT NULL,
  officeCode varchar(10) NOT NULL,
  reportsTo int(11) default NULL,
  jobTitle varchar(50) NOT NULL,
  PRIMARY KEY  (employeeNumber)
)

 CREATE TABLE employees_audit ( 
 id int(11) NOT NULL AUTO_INCREMENT, 
 employeeNumber int(11) NOT NULL, 
 lastname varchar(50) NOT NULL, 
 changedon datetime DEFAULT NULL, 
 action varchar(50) DEFAULT NULL, 
 PRIMARY KEY (id) 
 ) 

Then I created the trigger:

DELIMITER $$
 CREATE TRIGGER before_employee_update 
 BEFORE UPDATE ON employees
 FOR EACH ROW BEGIN
 INSERT INTO employees_audit
 SET action = 'update',
 employeeNumber = OLD.employeeNumber,
 lastname = OLD.lastname,
 changedon = NOW(); END$$
 DELIMITER ;

Both First table and Second is created but when I execute the trigger I get a开发者_C百科n error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employe' at line 1 

Is there any error in my code or do I need another MySQL version?

Are you running this from phpMyAdmin? If so the delimiter should be set in a field below the SQL textarea not in the text area itself.

And no. Contrary to the answer that has been deleted, there is no ; needed after DELIMITER $$ because that would set the delimiter to $$;

well, I have got what you are doing wrong. See the execution below:

mysql>  CREATE TABLE employees_audit (
->  id int(11) NOT NULL AUTO_INCREMENT,
->  employeeNumber int(11) NOT NULL,
->  lastname varchar(50) NOT NULL,
->  changedon datetime DEFAULT NULL,
->  action varchar(50) DEFAULT NULL,
->  PRIMARY KEY (id)
->  )
->
-> DELIMITER $$
->  CREATE TRIGGER before_employee_update
->  BEFORE UPDATE ON employees
->  FOR EACH ROW BEGIN
->  INSERT INTO employees_audit
->  SET action = 'update',
->  employeeNumber = OLD.employeeNumber,
->  lastname = OLD.lastname,
->  changedon = NOW(); END$$

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIM ITER $$

RANG A BELL? LOOK SIMILAR?

Buddy, you have (most likely) forgotten to put a ; at the end of create table directive. The below code works:

    mysql>
    mysql>  CREATE TABLE employees_audit (
        ->  id int(11) NOT NULL AUTO_INCREMENT,
        ->  employeeNumber int(11) NOT NULL,
        ->  lastname varchar(50) NOT NULL,
        ->  changedon datetime DEFAULT NULL,
        ->  action varchar(50) DEFAULT NULL,
        ->  PRIMARY KEY (id)
        ->  );
    Query OK, 0 rows affected (0.09 sec)

    mysql>
    mysql> DELIMITER $$
    mysql>  CREATE TRIGGER before_employee_update
        ->  BEFORE UPDATE ON employees
        ->  FOR EACH ROW BEGIN
        ->  INSERT INTO employees_audit
        ->  SET action = 'update',
        ->  employeeNumber = OLD.employeeNumber,
        ->  lastname = OLD.lastname,
        ->  changedon = NOW(); END$$
    Query OK, 0 rows affected (0.01 sec)

    mysql>  DELIMITER ;

OLD ANSWER

try this

DELIMITER $$
 CREATE TRIGGER before_employee_update BEFORE UPDATE ON employees
 FOR EACH ROW 
 BEGIN
   INSERT INTO employees_audit
   SET action = 'update',
   employeeNumber = OLD.employeeNumber,
   lastname = OLD.lastname,
   changedon = NOW(); 
 END;$$
 DELIMITER ;

missing ; after END, I guess,

Edit: fixed foramtting

Edit1: re-answered.