Create Trigger cannot be used in a transaction or with other statements

[rmanibus]

Describe the problem

Executing a create trigger inside a transaction, or next to another statement fails with:
ERROR: CREATE TRIGGER is only implemented in the declarative schema changer
SQLSTATE: 0A000

I get that I should use the declarative schema changer, but it is confusing to understand when it is used or not.
The only way I managed to create a trigger is to run it in total isolation.

Please describe the issue you observed, and any steps we can take to reproduce it:

To Reproduce

begin;
create trigger the_table_trigger after insert or update or delete
                                            on the_table
                                                for each row
execute function the_table_journal_function();
commit;

What did you do? Describe in your own words.

If possible, provide steps to reproduce the behavior:

1. Set up CockroachDB cluster ...
2. Send SQL ... / CLI command ...
3. Look at UI / log file / client app ...
4. See error

Expected behavior
A clear and concise description of what you expected to happen.

Additional data / screenshots
If the problem is SQL-related, include a copy of the SQL query and the schema
of the supporting tables.

If a node in your cluster encountered a fatal error, supply the contents of the
log directories (at minimum of the affected node(s), but preferably all nodes).

Note that log files can contain confidential information. Please continue
creating this issue, but contact support@cockroachlabs.com to submit the log
files in private.

If applicable, add screenshots to help explain your problem.

Environment:

• CockroachDB version [e.g. 2.0.x]
• Server OS: [e.g. Linux/Distrib]
• Client app [e.g. cockroach sql, JDBC, ...]

Additional context
What was the impact?

Add any other context about the problem here.

Jira issue: CRDB-46975

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: schema change)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[rafiss]

Thanks for this report! I agree the error message is not helpful for figuring out how to make it work. The details that would be helpful are at this section of the docs: https://www.cockroachlabs.com/docs/stable/online-schema-changes#declarative-schema-changer

We should be able to update the error message to include this information or reference these docs.

[Dedej-Bergin]

I think the confusion here is now removed, due to the autocommit_ddl being now set to true by default. Will confirm this and will update this comment.

[spilchen]

Enabling the autocommit_before_ddl setting may reduce the frequency of this message, but it doesn't eliminate potential confusion. Also, similar messages appear for other operations, such as CREATE POLICY. So, we should look at making a general fix for this.