Question: I have two tables in mysql. When I insert/delete values in the first table I want that the values get duplicated in table 2 to keep them "aligned".

table1:  

id - username  

1 - test\_user  

table2:  

Same id as table1 and username as table1 (on insert/delete)  

I want to keep the data between the tables aligned without doing multiple queries. I've read about triggers not sure if it's the correct road, i am a beninner.  

I said two tables but i will need to do this in multiple tables.

Answer: 
When you `INSERT` new records, given that you don't want to do two inserts for some reason, [using a trigger](http://dev.mysql.com/doc/refman/5.0/en/triggers.html) to insert into the second table will work. For `UPDATE` and `DELETE` you might want to look at the `CASCADE` option with [foreign keys](http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html). If all you are doing is keeping the data consistent between tables, that's exactly what cascade is for.

When you create table2 you just add a foreign key like this:

```
FOREIGN KEY (id, username) 
  REFERENCES table1(id, username) ON UPDATE CASCADE ON DELETE CASCADE

```

Then whenever you alter table1 the changes will automatically get pushed through to table2. 

**Couple prerequisites for this to work:**

1. You have to use a [storage engine](http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html) that supports foreign keys, something like `InnoDB` and not `MyISAM`
2. You need to have an index on `(id,username)` in table1; the foriegn key needs to match a key in the parent table
3. You should read the doc page for [foreign keys](http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html). There are a couple other ways you can tweak them, and you should figure out what works best for your purposes.
You can certainly put triggers on your table1 to make parallel changes to your other tables as your application changes table1.

See here for the documentation: <http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html>

But, you should think over your design. It will take multiple queries to do your inserts and updates; they'll just be done "behind your back" on the server. They'll still take time. Triggers can really slow things down.

Also, triggers are a little bit fragile. If you add a column to a table, you'll have to rework your triggers. Triggers are generally a pain in the neck to keep in a source-control system and a huge pain in the neck to test, so using them will make your application more troublesome to maintain.

Could you think of another approach to handling this need for duplication? Could you, for example, use a view or a join to present the data you need to your application program without actually duplicating tables and the rows in them? If you figure out how to do that you'll be much happier in the long run.

```
 CREATE VIEW table2 AS 
      SELECT * 
       FROM table1;

```

will produce a "fake" table2 with the contents of table1.

Or if you're hoping to view only the test users in a second table, a view can do that for you too, for example:

```
CREATE VIEW table3 AS
     SELECT * 
       FROM table1
      WHERE usertype = 'test_user' ;

```

If you're using duplicate tables for "backup," that's a bad way to make sure your information is safe. Instead, you need to back up your MySQL server instance.

Formal relational database design principles teach us to duplicating data, but instead use view and joins to structure the data the way applications need to see it.