We help IT Professionals succeed at work.

Creating a MySQL Trigger

medievalman
medievalman asked
on
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
Comment
Watch Question

View Solution Only

Distinguished Expert 2019
Commented:
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