I have submissions of a form from a web page records in a database.
the table has the following schema
submit_time form_name field_name field_value field_order file
the form has 5 fields, each form submission results in 5 rows in the previous table, one row per field. the following is an example of 5 rows created after a submission.
submit_time form_name field_name field_value field_order file 1427715050.2890 Contact form 1 your-name Jung 0 NULL 1427715050.2890 Contact form 1 your-email [email protected] 1 NULL 1427715050.2890 Contact form 1 your-subject Jung maybe :) 2 NULL 1427715050.2890 Contact form 1 your-message Jung :D 3 NULL 1427715050.2890 Contact form 1 Submitted Login Mohammad 9999 NULL
I want the data to be written in another table in another database the new table has the following schema:
Name email subject message Login
What I tried
I thought about writing a trigger like the following:
CREATE TRIGGER copy_data AFTER INSERT ON TABLE_A FOR EACH ROW BEGIN # check if submission entry exists in table B # NO : # INSERT the data in the corresponding column # YES: # UPDATE the data in the corresponding column END
but I can't insert NULL values in the second table.
what can I do in such situation ?
Note: I can't guarantee the order of the rows in Table A ( which field will be inserted first is something I can't control)