Triggers and scheduled events

What a trigger is

A trigger is code the engine runs by itself, before or after an INSERT, UPDATE or DELETE on one table. Nobody calls it: the statement that touched the table sets it off, and it runs inside that statement's transaction (see Transactions and concurrency). NEW is the incoming row, OLD is the row as it was. A BEFORE trigger may change NEW or refuse the change with an error, and that error fails the original statement.

Four typical jobs:

• Audit trail. Copy who changed what, and when, into an orders_audit table.
• Derived column. Keep orders.total equal to the sum of its order_items.
• Validation beyond CHECK. Compare old and new values, then refuse with SIGNAL in MySQL or RAISE in PostgreSQL.
• Cross-table bookkeeping. Decrement stock, bump a counter, mark a customer active.

Keeping orders.total honest

Order 12 holds two mice at 20.00, so total is 40.00. Add a 45.00 keyboard, and an AFTER INSERT trigger on order_items corrects total in the same transaction.

-- NEW is the row that was just inserted into order_items
CREATE TRIGGER trg_items_ai
AFTER INSERT ON order_items
FOR EACH ROW
  UPDATE orders
  SET total = total + NEW.qty * NEW.unit_price
  WHERE id = NEW.order_id;

INSERT INTO order_items (order_id, product_id, qty, unit_price)
VALUES (12, 100, 1, 45.00);   -- a keyboard for Ben's order

PostgreSQL splits this in two: the body lives in a trigger function returning type trigger, and CREATE TRIGGER says when to call it (see Stored procedures and functions).

What each engine can do

Both engines have triggers. PostgreSQL has the extras. This table is the map.

Two rows are worth knowing by name: PostgreSQL constraint triggers can be deferred to COMMIT, and its event triggers fire on DDL.

Trigger hazards

• Hidden logic. A developer updates one column and finds three others changed. When a deadlock report names a table nobody's code mentions, suspect a trigger.
• Bulk loads. A million rows means a million trigger runs, each with its own writes and locks.
• Recursion. MySQL refuses to let a trigger modify a table the invoking statement is already using; PostgreSQL lets triggers nest, so guard with pg_trigger_depth().
• No transaction control. MySQL forbids START TRANSACTION, COMMIT and ROLLBACK in a trigger body. Raising an error is all a trigger can do.

Finding the triggers you inherited

Assume every important table may have triggers until you have looked. Both catalogs answer in seconds.

SHOW TRIGGERS FROM shop;                   -- one row per trigger, with the body

SELECT TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING, ACTION_ORDER, DEFINER
FROM information_schema.TRIGGERS
WHERE EVENT_OBJECT_SCHEMA = 'shop' AND EVENT_OBJECT_TABLE = 'order_items';

DROP TRIGGER shop.trg_items_ai;            -- the only way to switch it off

MySQL cannot pause a trigger: drop and recreate it around a bulk load, or guard the body with a session variable. PostgreSQL disables one in place.

\d order_items          -- psql lists a "Triggers:" block at the bottom

SELECT tgname, tgenabled            -- 'O' = enabled, 'D' = disabled
FROM pg_trigger
WHERE tgrelid = 'order_items'::regclass AND NOT tgisinternal;

ALTER TABLE order_items DISABLE TRIGGER trg_items_ai;   -- pause it
ALTER TABLE order_items DISABLE TRIGGER USER;           -- all yours, not the FK ones

USER leaves the internal triggers that implement foreign keys alone.

Scheduled jobs

MySQL has a built-in Event Scheduler: a server thread that runs each named event's SQL once, or on a repeating interval. It is on by default (event_scheduler=ON), creating one needs the EVENT privilege, and it runs as its DEFINER. Typical jobs are housekeeping: purge old rows, refresh a summary table, expire sessions.

CREATE EVENT purge_cancelled_orders
ON SCHEDULE EVERY 1 DAY STARTS '2026-10-01 03:30:00'
DO
  DELETE FROM orders
  WHERE status = 'cancelled'
    AND order_date < CURRENT_DATE - INTERVAL 1 YEAR;

SHOW EVENTS FROM shop;
SELECT EVENT_NAME, STATUS, LAST_EXECUTED, DEFINER
FROM information_schema.EVENTS WHERE EVENT_SCHEMA = 'shop';
ALTER EVENT purge_cancelled_orders DISABLE;   -- keep it, stop it running

LAST_EXECUTED is NULL until the first run, then it is your cheapest health check: older than the interval means something is wrong.

PostgreSQL has no built-in scheduler. The usual answer is pg_cron, an extension loaded at startup through shared_preload_libraries; it takes cron expressions and logs every run in cron.job_run_details. It is not in the Windows EDB installer, so on a Windows server you will more often let Task Scheduler (or schtasks) run psql or mysql with a script file.

# Stored programs are not dumped unless you ask for them
mysqldump --login-path=backup --single-transaction --routines --events --triggers shop --result-file=C:\backups\shop.sql

The DBA angle

Triggers and events are the parts of a schema most easily forgotten, because they work without anyone touching them.

• They run as their definer. Check DEFINER in information_schema.TRIGGERS and information_schema.EVENTS before you touch a leaver's login (see Users, roles and security).
• They fail quietly. A failed MySQL event writes to the server error log, hostname.err in the data directory, and nowhere else. Nothing pages you.
• Replicas keep a disabled copy. Events arrive with status REPLICA_SIDE_DISABLED, so a promoted replica needs ALTER EVENT … ENABLE. Put it on the failover checklist.

• MySQL 8.4: CREATE TRIGGER

  Syntax, ordering, DEFINER rules.

• PostgreSQL: CREATE TRIGGER

  Statement level, INSTEAD OF, transition tables.

• MySQL 8.4: Using the Event Scheduler

  Configuration, syntax, metadata, privileges.

• pg_cron on GitHub

  Install notes and cron.schedule() examples.

• Microsoft: schtasks

  The command line face of Task Scheduler.