Mysql trigger - simple question

[miklas]

 

 

Hi,

 

I have one table (TABLE A) in my mysql database which has the listed fields:

 

    * row1_id - PK

    * row2_id - PK

    * row3_data

 

What I would like to do is, every time a record is inserted or updated in table A, I would like to call a trigger to write me the new information of table A in another table, TABLE A_LOG, which has the following fields:

 

    * row0-modification-id - PK

    * row1_id (from table A) - PK

    * row2_id (from table A) - PK

    * row3_data (from table A)

 

the Table A_LOG should then have 3 PKeys, two from the new inserted/updated record of table A and other that indicates me the number of the modification (1, 2, 3..)

 

Thank you very much for your help.

 

[Mchl]

What MySQL version you have? Triggers are available since 5.0.2

 

DELIMITER ||
CREATE TRIGGER `A_afterInsert` AFTER INSERT ON `A`
  FOR EACH ROW BEGIN
    INSERT INTO `A_LOG` (row1_id,row2_id,row3_data) VALUES (NEW.row1_id,NEW.row2_id,NEW.row3_data);
  END;
||

CREATE TRIGGER `A_afterUpdate` AFTER UPDATE ON `A`
  FOR EACH ROW BEGIN
    INSERT INTO `A_LOG` (row1_id,row2_id,row3_data) VALUES (NEW.row1_id,NEW.row2_id,NEW.row3_data);
  END;
||

DELIMITER ;

 

 

[miklas]

Hi,

 

thank you very much for your quick reply.

My sql version is fine, triggers work fine

 

It seems like this is it. But I didn't see any reference to the row0_id there. this will work anyway?

 

thks

 

What MySQL version you have? Triggers are available since 5.0.2

 

DELIMITER ||
CREATE TRIGGER `A_afterInsert` AFTER INSERT ON `A'
  FOR EACH ROW BEGIN
    INSERT INTO `A_LOG` (row1_id,row2_id,row3_data) VALUES (NEW.row1_id,NEW.row2_id,NEW.row3_data);
  END;
||

CREATE TRIGGER `A_afterUpdate` AFTER UPDATE ON `A`
  FOR EACH ROW BEGIN
    INSERT INTO `A_LOG` (row1_id,row2_id,row3_data) VALUES (NEW.row1_id,NEW.row2_id,NEW.row3_data);
  END;
||

DELIMITER ;

[Mchl]

I assumed row0_id is an AUTO_INCREMENT column.

[miklas]

You are correct, I didn't figure it out in the first place thank you very much for your help, my problem is solved

 

 

I assumed row0_id is an AUTO_INCREMENT column.