1

I am trying to create a trigger in mysql using the following:

CREATE TRIGGER ins_daft BEFORE INSERT ON jos_ezrealty
FOR EACH ROW BEGIN 
SET preschool = livingarea*10.76391041671
END;

When I do I get the following error:

Error
SQL query:

CREATE TRIGGER ins_daft BEFORE INSERT ON jos_ezrealty
FOR EACH
ROW BEGIN 
SET preschool = livingarea * 10.76391041671 END

MySQL said: Documentation

#1193 - Unknown system variable 'preschool'

I am trying to have the value of one field converted to square feet by multiplying by 10.76391041671. Can anyone see what I am doing wrong?

Thank you.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
CDubYaa
  • 45
  • 1
  • 10

1 Answers1

4

Any time you want to reference the columns of a row that fired the trigger, qualify them like NEW.column_name.

Otherwise the SET command thinks you want to set a MySQL configuration variable called preschool.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I made the change and now I get this:SQL query: CREATE TRIGGER ins_daft BEFORE INSERT ON jos_ezrealty FOR EACH ROW BEGIN SET NEW.preschool = 'livingarea*10.76391041671' END MySQL said: Documentation #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 'END' at line 3 – CDubYaa Sep 22 '14 at 23:31
  • Each statement in the body of the trigger needs a semicolon (`;`) to terminate it. – Bill Karwin Sep 23 '14 at 01:23