SQLcl: MCP Server & SQL Prompt

TL;DR : when generating a schema, TRIGGERs seem to be exported in alphabetical order without consideration of trigger dependency/ordering (i.e. use of the FOLLOWS clause) which causes warning messages when deploying and INVALID triggers in the database.

Test Case

Issue is easy to illustrate using a simple test case.

SQLcl version: 25.1.1.0 build: 25.1.1.113.2054

First install the sample SCOTT schema using @?/rdbms/admin/utlsampl.sql and then connect as SCOTT:

-- Create a trigger:
create or replace trigger runs_first before insert on emp for each row
begin
  null;
end;
/

-- Create a second trigger that has an alphabetically sorted earlier name and that uses the FOLLOWS clause:
create or replace trigger runs_after before insert on emp for each row
follows runs_first
begin
  null;
end;
/

-- Generate the schema using Liquibase:
lb generate-schema -overwrite-files -split -replace

In another environment, create the SCOTT user without any tables (or just drop all SCOTT owned objects in the current database) and try to create them using Liquibase:

SQL> lb update -changelog-file controller.xml

…
LINE/COL  ERROR
--------- -------------------------------------------------------------
0/0       Trigger "SCOTT"."RUNS_FIRST" referenced in FOLLOWS or PRECEDES clause may not exist
Errors: check compiler log
…

And the result is that the second trigger is added to the database but is INVALID:

SQL>  select object_name, status from user_objects where object_type = 'TRIGGER';
OBJECT_NAME                      STATUS
________________________________ __________
DATABASECHANGELOG_ACTIONS_TRG    VALID
RUNS_FIRST                       VALID
RUNS_AFTER                       INVALID

Manually fix the order in the controller.xml file, or alternatively name the triggers in an order where dependent triggers come alphabetically last, and there will be no warning, and all triggers will be added and will be VALID in the database.

Conclusion

I believe that this is a small bug (or ER) in the liquibase SQLcl command. When determining the object order for the controller.xml file, it current is not, but should be enhanced to, consider trigger order vs pure alphabetical order.