🐛 BUG: D1 Database triggers bypass D1 data type validation

[conqr2]

Which Cloudflare product(s) does this pertain to?

D1

What version(s) of the tool(s) are you using?

3.53.1 [Wrangler]

What version of Node are you using?

No response

What operating system and version are you using?

Mac Ventura 13.5.2

Describe the Bug

Observed behavior

Triggers can bypass column data type validation when updating a row

Expected behavior

Triggers should be restricted by a column's data type when performing updates

Steps to reproduce

• Setup:

CREATE TABLE IF NOT EXISTS test (
    id TEXT PRIMARY KEY NOT NULL,
    val INTEGER NOT NULL
);

CREATE TRIGGER update_on_change AFTER
UPDATE OF val ON test FOR EACH ROW
BEGIN
UPDATE test
SET
    val = 'not_an_integer'
WHERE
    id = NEW.id;

END;

INSERT INTO test (id, val) VALUES ('test', 1);

• Trigger the trigger:

UPDATE test SET val=2 WHERE id='test'

• the val column of the test row (val is defined as an Integer) now contains Text

Please provide a link to a minimal reproduction

No response

Please provide any relevant error logs

No response