How to handle SQL triggers in Symfony2?

Question

I have a User object in my Symfony2 / Doctrine2 web application. This user has the last_updated attribute to identify the last time everything has changed. I set this attribute to NOT NULL in my database. So far so good.

I find it good practice to create an SQL trigger in a database that sets this last_updated to NOW() on all INSERT or UPDATE . Therefore, you do not need to take care of this in your application. So what I did, I implemented this trigger in my database.

But if now I create a user in my application

 $user = new User(); $em = $this->getDoctrine()->getManager(); $em->persist($user); $em->flush(); 

I get an error from Symfony:

An exception occurred while executing 'INSERT INTO User (username, ..., last_updated) VALUES (?, ..., ?)' With parameters ["johndoe", ..., null]:

SQLSTATE [23000]: Violation of integrity constraint: 1048 Column 'last_updated' cannot be null

The problem is obvious: Symfony is trying to run the INSERT -statement in the database with the null parameter for last_updated , which is not valid because this attribute may not be null.

I could quickly think of two workarounds:

• A last_updated would be to exclude the last_updated field from the entity description. Then Symfony will not try to transfer anything to the database for this column, and the trigger will set the corresponding value. But I don't think this is a good way, because as soon as I try to update the db schema ( doctrine:schema:update --force ), I will lose my last_updated -column.
• Another workaround: just $user->setLastUpdated(new \DateTime()) before I persist() and flush() . But this will minimize the advantage of using a trigger in my database so as not to worry about it in my application.

Is there a way to tell Symfony / Doctrine that a trigger is running in my database? If not, (how) can I connect to Symfony / Doctrine to implement the proper workaround?

Answer 1

To quote the answer to this question in the google group:

Database base code (such as triggers and functions) tends to violate the benefits of developing software using ORM, such as Propel or Doctrine, as one of the biggest advantages of using ORM is database agnostic. With triggers and functions on the database side, you bind yourself to the database and therefore get little benefit from using ORM. -GarethMc

https://groups.google.com/forum/#!topic/symfony-users/MH_ML9Dy0Rw

For this, it's best to use lifecycle callbacks, as Fairy suggests. One simple function will handle updating this field, so you don’t have to worry about it if you decide to change the databases in the future.

 //In Your Entity File EX: SomeClass.php /** * @ORM\Entity * @ORM\HasLifecycleCallbacks() */ class SomeClass { .... /** * @ORM\PrePersist() * @ORM\PreUpdate() */ public function prePersistPreUpdate() { $this->last_modified = new \DateTime(); } } 

See also links for lifecycle callbacks

• Symfony Help
• Link to Doctrine

In your case, you add a lifecycle callback function and annotation to the user entity class. SomeClass is just an example class showing that lifecycle callbacks are not only good for your User object.

Answer 2

Another (simpler and more generalized) option would be to use the Gedmo Timestampable Doctrine extension. That way, you can simply annotate your entity fields as timestamps when creating or updating.

Example:

 use Doctrine\ORM\Mapping as ORM; use Gedmo\Mapping\Annotation as Gedmo; class MyEntity { ... /** * @var \DateTime $lastUpdated * * @Gedmo\Timestampable(on="update") * @ORM\Column(name="last_updated", type="datetime") */ private $lastUpdated; ... } 

https://packagist.org/packages/gedmo/doctrine-extensions