Mysql trigger or coding in PHP?

Question

I have a table hardware_description:

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| Computer_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| Emp_id           | int(11)      | NO   | MUL | NULL    |                |
| PC_type          | varchar(20)  | YES  |     | NULL    |                |
| Operating_system | varchar(20)  | YES  |     | NULL    |                |
| Product_key      | varchar(30)  | YES  |     | NULL    |                |
| Assign_date      | date         | YES  |     | NULL    |                |
| DVD_ROM          | varchar(20)  | YES  |     | NULL    |                |
| CPU              | varchar(30)  | YES  |     | NULL    |                |
| IP_address       | varchar(30)  | YES  |     | NULL    |                |
| MAC_address      | varchar(30)  | YES  |     | NULL    |                |
| Model_name       | varchar(30)  | YES  |     | NULL    |                |
| Model_number     | varchar(30)  | YES  |     | NULL    |                |
| Monitor          | varchar(30)  | YES  |     | NULL    |                |
| Processor        | varchar(30)  | YES  |     | NULL    |                |
| Product_name     | varchar(30)  | YES  |     | NULL    |                |
| RAM              | varchar(20)  | YES  |     | NULL    |                |
| Serial_number    | varchar(30)  | YES  |     | NULL    |                |
| Vendor_id        | varchar(30)  | YES  |     | NULL    |                |

Emp_id is foreign key from employees table.

When I update a particular row, I want the existing data for that row to be saved in another table along with the timestamp of that update action. Now,

a) Shall I use PHP code (PDO transaction) to first grab that row & insert in another table then perform the UPDATE query on that particular row?

b) Use trigger on this table.

Which process is better practice & more efficient? Is there another way of achieving this?

I have not used trigger in my short career so far but I can do it if it is better practice.

It depends on your situation and resources. Triggers are a good way of doing stuff like this, but it does mean, as your program evolves, that you will have to maintain two systems in parallel. It may or may not be something you want to do; only you know. — Sverri M. Olsen
– Sverri M. Olsen, Commented Dec 2, 2014 at 5:02

Zerium · Accepted Answer · 2014-12-02 04:51:51Z

1

If you can do a trigger, it would be a lot better to use that.

The reason for this is that if for some reason you forget to write the PHP code to do this (in some weird situation) - you would have missing, unrelated data - otherwise known as orphaned data, which does not have a corresponding row or set of rows.

Here's the link to the MySQL documentation page for triggers: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

answered Dec 2, 2014 at 4:51

Zerium

17.4k33 gold badges121 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql trigger or coding in PHP?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related