Need help with simple mysql triggers

Question

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.

Answer 1

Create TableA_Log to autoincrement row0-modification-id. I bet you don't exactly need this table to have row0-modification-id AND row1_id and row2_id all as the PK.

CREATE TRIGGER dbo.trg_TableA_ins_upd
   ON  dbo.DatabaseName
   AFTER INSERT, UPDATE
AS 
BEGIN
   INSERT INTO TableA_Log (row1_id, row2_id, row3_data) 
   SELECT row1_id, row2_id, row3_data FROM inserted
END