Creating a MySQL Trigger

I am trying to create a trigger in MySQL that copies a table row to a new database before and update occurs to use as a revisions system. I am using myphpadmin and the SQL is as follows:

delimiter //
CREATE TRIGGER uom_revision BEFORE UPDATE ON ttb_nhie_unitsofmeasure
	FOR EACH ROW
	BEGIN
	   	INSERT INTO ttb_nhie_unitsofmeasure_rev 
        SELECT FROM ttb_nhie_unitsofmeasure WHERE UnitOfMeasureId = OLD.UnitofMeasureID;
    END;
//
delimiter ;

Open in new window


I am receiving the following error when I try this:

#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 'FROM ttb_nhie_unitsofmeasure WHERE UnitOfMeasureId = OLD.UnitofMeasureId;
    E' at line 5

I am using MySQL version 5.6.17
medievalmanAsked:
Who is Participating?
 
arnoldCommented:
you seem to do a table while you are saying you want to do an update to a separate database.

your error is that you are not specifying the columns that will be inserted, nor are you listing the columns in the select statement.

delimiter //
CREATE TRIGGER uom_revision BEFORE UPDATE ON ttb_nhie_unitsofmeasure
	FOR EACH ROW
	BEGIN
	   	INSERT INTO ttb_nhie_unitsofmeasure_rev (column1, column2,column3)
        SELECT column_column1,column_column2,column_column3 FROM ttb_nhie_unitsofmeasure WHERE UnitOfMeasureId = OLD.UnitofMeasureID;
    END;
//
delimiter ;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here