I have MySQL database that has 21 tables, in each table I have 'created' column(timestamp). this field holds insert time of row.
However:
1- I can write a trigger for fill the column for each table like this:
CREATE TRIGGER `created` BEFORE INSERT ON `some_table`
FOR EACH ROW SET NEW.`created`=NOW()
2- I can handle it by PHP:
$result = mysqli_query($con,"INSERT INTO table_name (column1, column2, column3,created)
VALUES (value1, value2, value3,now()) ");
I concerned about writing trigger for each table has overhead. Is it right? which one is better specific on performance? which one is better at all?
You should not need to fill this column yourself if you configure the TIMESTAMP properties for it... http://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html
To clarify from the documentation here is a working example of how subsequent updates do not change a timestamp field when correctly configured for the desired behaviour
create temporary table example (apID int auto_increment,
audit timestamp DEFAULT CURRENT_TIMESTAMP,
name varchar(50),
primary key(apID));
insert into example(name) values('test');
select * from example;
update example set name='tes' where apID=1;
select * from example;