Triggers and events: multi-driver management in QoreDB

Triggers and scheduled events are among the least portable schema objects from one engine to another. The syntax changes, the vocabulary changes, the granularity changes. PostgreSQL talks about functions attached to a table, MySQL about inline code attached to a table plus events (scheduled tasks on the server side), SQL Server distinguishes DML and DDL triggers, and SQLite only knows very restricted SQL triggers. A general-purpose client has to decide how to expose all of this.

QoreDB's stance: show what each engine defines, in its own terms, and let the developer work in the vocabulary they already know.

Not hiding the differences between engines

A PostgreSQL trigger and a MySQL trigger are not the same thing. The former is an association between a table and a function (often in PL/pgSQL), the latter contains the SQL body directly in the definition. Merging them into an intermediate model would produce an object that correctly represents neither one, and that would force inventing concepts that do not exist.

The TriggerDefinition structure on the Rust side retains only what makes sense everywhere: the name, the namespace (database + schema), the target table, the timing (BEFORE, AFTER, INSTEAD OF), the list of events (INSERT, UPDATE, DELETE, TRUNCATE), the enabled/disabled state, and above all the raw text of the CREATE TRIGGER as the engine returns it. This text is not reprocessed. It is the engine's truth, not a reconstruction.

Concretely, PostgreSQL uses pg_get_triggerdef(oid) to render the definition, MySQL goes through SHOW CREATE TRIGGER, and SQLite reads sqlite_master. Each has its own source, each has its own syntax. QoreDB displays what the server produces, with no intermediate normalization.

The driver contract

Each driver implements the DataEngine trait and decides whether it handles triggers via supports_triggers() and events via supports_events(). The default implementation returns false, which means the Triggers tab does not appear for that engine. No UI code trying to guess, no button grayed out for no reason: the capability comes down from the driver.

The relational engines in the PostgreSQL family (Postgres, Neon, Supabase, TimescaleDB, CockroachDB) share a common implementation located in pg_compat. It queries pg_trigger and information_schema to list, and pg_get_triggerdef to render. MySQL and MariaDB also share their code, with a fallback to SHOW CREATE. SQL Server has its own implementation over sys.triggers and sys.sql_modules. SQLite handles reading and dropping, but not enabling/disabling, because SQLite does not expose that notion. The NoSQL engines (MongoDB, Redis) and DuckDB expose nothing: the trait returns false and the tab disappears.

This model has a direct consequence: adding support for a new engine goes solely through implementing the trait. The UI does not need to be modified; it reacts to what the driver declares.

Scheduled events, a case apart

Scheduled events (the server-side scheduler) are a MySQL and MariaDB specificity. They are exposed in information_schema.EVENTS with a status of ENABLED, DISABLED, or SLAVESIDE_DISABLED, an interval, and a time expression. QoreDB lists them in the same tab as the triggers, because the two concepts live together in a MySQL database.

PostgreSQL has no native equivalent: there is pg_cron as an extension, or external schedulers. These objects are not exposed as events in QoreDB, because they share neither the semantics, nor the lifecycle, nor the standardized metadata. The day a concrete need for pg_cron inspection emerges, it will be handled as a driver extension, not as a veneer over the event abstraction.

Drop and toggle go through the interceptor

Dropping a trigger or an event is a destructive DDL operation. The backend's drop_trigger and drop_event commands do not bypass the safeguards: they build an interceptor context with the session's environment (Dev, Staging, Prod), the read-only state, and the SQL preview of type DROP TRIGGER. The Universal Query Interceptor evaluates the security rules before execution and can block, require a confirmation, or simply warn. The result goes back through post_execute for history and tracing.

The toggle (enable/disable) is lighter on the safety side, because it is reversible without loss, but it is still blocked in read-only mode. This difference in treatment is deliberate: confirmation costs time, so it is required only when reversibility disappears.

In the interface

The Triggers tab appears in the Database Browser only when the driver declares support. The list shows the name, the target table, the timing, the associated events, and the enabled state. A context menu lets you open the source (the engine's raw CREATE), disable or enable, and delete. On MySQL and MariaDB, the same view lists the events with their schedule and their status.

Opening the source reformats nothing. It is the server's text, ready to be copied into an editor, exported to a migration file, or versioned in a repository. For a developer auditing a schema or preparing a rollback, this transparency avoids nasty surprises when replaying a CREATE TRIGGER.

The management of triggers and events in QoreDB reflects the principle that guides the whole tool: expose what the engine provides, respect its terminology, and add abstraction only where it simplifies without lying. A trigger seen in QoreDB is a trigger as the server describes it, and destructive operations remain governed by the same interceptor chain as user queries. That is what makes the behavior predictable from one engine to another.