mysql trigger or php code

Question

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?

Steph Locke · Accepted Answer · 2013-06-11 15:49:23Z

3

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;

edited Jun 11, 2013 at 15:49

answered Jun 11, 2013 at 9:45

Steph Locke

6,2064 gold badges44 silver badges79 bronze badges

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

4 Comments

Arash Mousavi Over a year ago

I know that! but it update the field for each UPDATE query, not just for INSERT. If you read my question carefully I said the 'created' field holds insert time, not every update time!

Steph Locke Over a year ago

And in the documentation it says that the UPDATE only happens when it is configured to do so - therefore if it is currently changing on UPDATE, change your DDL. Apologies but I don't see how I misread it - you're asking after how you can populate this field as far as I can tell as you only mention INSERTs with no DUPLCIATE KEY mentions. If however this is not case, please update your question to more clearly indicate what you're after.

Arash Mousavi Over a year ago

If you read the documentation carefully,by "configuring the TIMESTAMP properties"(as you said) you can't prevent mysql to update this column in UPDATE queries that changes other columns. it is simple question: I want to hold only insert time in 'created' column...

Steph Locke Over a year ago

@ArashMousavi -I have updated my answer to show the behaviour that the doc states: •With a DEFAULT clause but no ON UPDATE CURRENT_TIMESTAMP clause, the column has the given default value and is not automatically updated to the current timestamp.

Collectives™ on Stack Overflow

mysql trigger or php code

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related