Reconnecting Symfony with Your Database: The Power of SQL Triggers
Published on August 27, 2025
It all starts with a familiar situation: a critical business logic, auditing a modification, updating a counter, or validating complex data that you want to execute atomically and infallibly with every database change. The most robust technical solution is often an SQL trigger. However, we don't always dare to use them in a Symfony project because managing them can quickly become a real headache. The SQL code gets lost because it's not versioned or is hidden away in a migration file, and the developer modifying a Doctrine entity sometimes has no idea about the logic being triggered in the database.
This gap between our PHP application and our database creates an insidious technical debt. We end up re-implementing logic on the application side that would be far more robust and efficient at the database level. The Trigger Mapping Bundle was created to try to bridge this gap. The idea is simple: to reconcile our Symfony applications with SQL triggers.
#The Anatomy of SQL Triggers
Before diving into the solution, let's have a brief reminder of how they work.
A trigger is a block of business logic code (in this case, an SQL query) that executes automatically when a specific event occurs on a table or view.
The foundation of a trigger is that it is automatic: it is not called directly by the application but is "triggered" by the RDBMS itself. To summarize: whatever happens (or doesn't happen) in your application or in any other service connected to your database, if a trigger is listening for insertions in a table (for example), it will systematically be executed.
Its basic syntax revolves around three key components:
- The event (
on): The action that activates the trigger. This is usually a data manipulation operation (INSERT,UPDATE, orDELETE). - The execution time (
when): Specifies whether the trigger should run just before (BEFORE) or just after (AFTER) the event has occurred. - The target table: The table to which the trigger is attached.
The power of triggers also (and especially) lies in the OLD and NEW concepts. These structures, available within the trigger's body, allow access to the data of the row affected by the operation.
NEWcontains the row's values after the modification (for anINSERTorUPDATE).OLDcontains the row's values before the modification (for anUPDATEorDELETE).
This ability to compare the before and after states is at the heart of most trigger logic, allowing, for example, to check if a specific field has changed (OLD.price <> NEW.price) or to archive old data before its deletion.
Triggers are powerful, almost perfect when database modifications can come from both our application and an external script or SQL migration. However, they are not without flaws: a poorly designed, overly complex trigger, or a cascade of triggers can seriously slow down operations. Furthermore, they tend to become "invisible" in our Symfony projects, confined to the DB schema.
The Great Forgotten of Our Projects
In a classic Symfony/Doctrine project, there isn't really a bridge between an entity and a trigger. This absence is quite logical: an ORM like Doctrine aims to be database-agnostic to ensure its portability.
We then find ourselves with somewhat clunky solutions. We can implement them in Doctrine migrations by adding a CREATE TRIGGER statement, but the syntax is often problematic due to delimiters, and more importantly, the trigger's logic is buried in a file that will quickly be forgotten.
The preferred solution is usually to turn to Doctrine's lifecycle events (prePersist, postUpdate, etc.). This is a good solution for application logic, but they are not triggers. Their execution is not guaranteed if a modification is made outside your application (via a script, another service, or directly in the DB), and they do not benefit from the same atomicity as the RDBMS provides.
An Attempt at a Solution: Declarative Mapping with PHP Attributes
The Trigger Mapping Bundle aims to change the game by introducing a declarative approach, directly inspired by how Doctrine maps entities and their properties.
No more lost SQL files; everything now happens right above your entity class thanks to a PHP attribute:
namespace App\Entity;
use Talleu\TriggerMapping\Attribute\Trigger;
use App\Triggers\MyAwesomeTrigger;
#[Trigger(
name: 'trg_user_updated_at',
when: 'AFTER',
on: ['INSERT', 'UPDATE'],
function: 'fn_update_timestamp_func',
className: MyAwesomeTrigger::class
)]
class User
{
// ...
}The benefits are immediate:
- Visibility: Any developer opening the User.php file instantly sees that a trigger is associated with this entity. The logic is no longer hidden.
- Versioning: The attribute is part of the PHP code, so it is versioned with Git. The change history of your triggers is as clear as that of your entities.
We move from an imperative approach ("execute this SQL") to a declarative approach ("this is what the final state should look like"). It's the philosophy of Doctrine, extended to triggers.
A Toolbox to Make Your Life Easier
The bundle doesn't just map; it comes with a series of commands to manage the lifecycle of your triggers.
bin/console triggers:mapping:updateThis is the "magic" command for projects that already have triggers in production. It scans your database, finds triggers not yet mapped by the bundle, and with the right options (--apply --create-files), it will:
- Identify the corresponding Doctrine entity.
- Automatically add the #[Trigger] attribute to the PHP class.
- Extract the trigger's SQL code and save it to a local file (by default in a PHP class), ready to be versioned.
In a single command, you can absorb all your technical debt.
bin/console make:triggerThe easiest way to get started. Integrated with Symfony's MakerBundle, this command launches an interactive wizard that guides you through creating a new trigger. It asks the right questions (on which entity, what name, which events...) and generates the attribute on the entity and the corresponding logic file (.sql or PHP class) for you.
bin/console triggers:schema:diffPrefer to write your mapping first? Add the #[Trigger] attribute to your entity, then run this command. It will generate the empty logic file and the necessary Doctrine migration to create the trigger in the database.
bin/console triggers:schema:validateThis command is a safety net to integrate into your CI. It compares the triggers declared in your code with those actually present in the database and alerts you to potential inconsistencies. No more nasty surprises in production.
Note that for now, the bundle does not compare the entire content of the functions executed by the triggers; it compares the called functions, the table, the timing, and generally everything related to the "definition" part of the trigger.
bin/console triggers:schema:updateThis command is the final step in deploying your triggers. Its purpose is to retrieve the definitions and function contents of all triggers declared on your entities and execute them directly on the database.
Quick Start
- Install via Composer:
composer require talleu/trigger-mapping - Configuration (optional): Create a
config/packages/trigger_mapping.yamlfile to define configuration info (trigger format, file locations, etc.).
# config/packages/trigger_mapping.yaml
trigger_mapping:
storage:
type: 'php'
namespace: 'App\Triggers'And that's it, the bundle is ready to use.
Trigger or Doctrine Listener: Which to Choose?
Now that triggers are easy to manage, the question is no longer "can we use them?" but "when should we use them?". Here is a guide to help you decide between a database trigger (managed by the bundle) and a Doctrine entity listener.
The Trigger Mapping Bundle doesn't reinvent the wheel; it aims to build the missing bridge between our Symfony applications and the power of our databases, making triggers visible, versionable, and manageable.
You can now choose the right tool for the right job, without compromise. For data integrity logic, use a trigger. For application-related business logic, keep your listeners.
The project is open-source and awaits your feedback and contributions!
