insert default values

(1) By anonymous on 2021-01-14 10:32:42 [link] [source]

Is there a reason why this works:

INSERT INTO ABC DEFAULT VALUES;

But this one gives me an error message:

CREATE TRIGGER T AFTER UPDATE ON ABC
FOR EACH ROW
BEGIN
  INSERT INTO ABC DEFAULT VALUES;
END;

Message:

SQL Error: near "DEFAULT": syntax error

(2) By Warren Young (wyoung) on 2021-01-14 10:58:33 in reply to 1 [source]

It’s explicitly called out in section 2.1 of the relevant doc page.

As for “why,” I think that’s due to a mismatch between what you expect it to do and what it’s actually saying. This UPDATE trigger doesn’t reference NEW or OLD, so if it worked, it would create a new row every time an existing row was updated.

Given the schema and your actual intent, I expect a useful trigger could be crafted.

(3) By anonymous on 2021-01-14 11:13:53 in reply to 2 [link] [source]

I wasn't aware that this behavior is explicitly called out. I guess I have to read through the docs more carefully.

I have some crazy recursive things to do. My example is just my minimal representation that triggers this behavior. Since this expression does not work within triggers, I have to add a dummy column to my table and do the following instead:

INSERT INTO(dummy) VALUES(NULL);