ALTER TABLE drops legacy ON UPDATE CURRENT_TIMESTAMP triggers #547

Problem

Rebuilding a table created by the legacy driver silently removes its ON UPDATE CURRENT_TIMESTAMP behavior. This affects ALTER TABLE, OPTIMIZE TABLE, and REPAIR TABLE.

The legacy driver implements this behavior with SQLite triggers. When the AST driver reconstructs the information schema, it does not recover the ON UPDATE metadata from those triggers. The triggers continue working until a table rebuild drops them.

Reproduction

Using the legacy driver from v2.2.23, run:

CREATE TABLE trigger_demo (
    id INT PRIMARY KEY,
    updated_at TIMESTAMP NOT NULL DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO trigger_demo (id) VALUES (1);

The legacy driver creates a trigger named __trigger_demo_updated_at_on_update__.

Reopen the same database with the AST driver, then run:

ALTER TABLE trigger_demo ADD COLUMN note TEXT;
UPDATE trigger_demo SET id = 2 WHERE id = 1;
SELECT updated_at FROM trigger_demo;
-- 0

Expected: updated_at changes to the current timestamp.

Actual: It remains 0. A native SQLite query confirms that the trigger is gone:

SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'trigger_demo';
-- No rows.

On separate copies of the original database, replacing the ALTER TABLE statement with OPTIMIZE TABLE trigger_demo or REPAIR TABLE trigger_demo has the same effect. Both report success.

Verified on trunk at e88a817e89a80c4fab2b46f86606367822695b45, with PHP 8.3.35 and SQLite 3.54.1. Updating a legacy table without rebuilding it still runs its trigger.

Expected behavior

An unrelated schema change or table maintenance operation should preserve automatic timestamp updates.

The shared recreate_table_from_information_schema() path drops the original table and recreates triggers only from information-schema metadata. The missing legacy metadata means no replacement is created.

Recover the ON UPDATE metadata from legacy triggers, or preserve those triggers during reconstruction. This can also affect sites already running the AST driver, because earlier upgrades retained the original tables and triggers.

Related

• Support ON UPDATE CURRENT_TIMESTAMP columns #148 added ON UPDATE CURRENT_TIMESTAMP support and is closed. This report concerns preserving that behavior in legacy tables.
• ALTER TABLE and OPTIMIZE TABLE reset the AUTO_INCREMENT counter #544 tracks another problem in the same rebuild path.
• Normalize zero defaults of temporal columns #542 adds an automatic upgrade migration that rebuilds tables with zero temporal defaults. This trigger loss was also reproduced during that migration.