title: MySQL: How to create trigger for setting creation date for new rows
body: <p>I ran into a problem as I tried to create two TIMESTAMP columns in my database. One called <code>created</code> and one called <code>updated</code>. I figured it would be easy to set the default value of both to <code>CURRENT_TIMESTAMP</code> and then <code>ON UPDATE CURRENT_TIMESTAMP</code> for the <code>updated</code> column. But for some reason MySQL means that's a bad idea... so I have been looking for ways to do this without having to set one of them in the insert query.</p>
  
  <p>I found one way by using a trigger in <a href="https://stackoverflow.com/questions/1281156/mysql-default-on-update-timestamps/1281159#1281159">this answer</a>, but I keep getting errors. I just managed to actually create the trigger, but now I get errors when I try to insert new rows claiming that</p>
  
  <blockquote>
    <p>1442 - Can't update table 'tasks' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. </p>
  </blockquote>
  
  <p>And I don't get what that means at all. So, I was hoping someone here could shed some light up this subject.</p>
  
  <p>The SQL I used to create the table and the trigger is as follows:</p>
  
  <pre><code>CREATE TABLE `tasks` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `created` DATETIME,
    `updated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `title` VARCHAR(255) NOT NULL,
    `notes` TEXT,
    `status_id` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    KEY `status_id` (`status_id`),
    CONSTRAINT `fk_tasks_statuses` FOREIGN KEY (`status_id`) REFERENCES `statuses` (`id`),
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  
  CREATE TRIGGER task_creation_timestamp AFTER INSERT ON tasks 
  FOR EACH ROW
  UPDATE tasks SET created = updated WHERE id = NEW.id;
  </code></pre>
  
  <p>What am I doing wrong here?</p>