Stored Procedures, Views, and Triggers in MySQL
Tables and columns are straightforward in Liquibase — one change type per operation, automatic rollback, clean history. Stored procedures, views, and triggers are different. They contain body SQL that uses semicolons internally, which conflicts with Liquibase’s default statement splitting. They are replaced rather than altered. And unlike tables, modifying them frequently over time is expected.
This article covers the mechanics and patterns that make stored objects manageable in Liquibase.
The Core Problem: Delimiter Conflicts
A stored procedure body contains semicolons to terminate each statement inside it:
CREATE PROCEDURE get_user_orders(IN p_user_id BIGINT)
BEGIN
SELECT * FROM orders WHERE user_id = p_user_id; -- semicolon here
SELECT * FROM payments WHERE user_id = p_user_id; -- and here
END;
By default, Liquibase splits SQL on semicolons (splitStatements: true) before sending them to the database. This means the procedure body is split into multiple partial statements and each fragment is sent independently — which fails immediately.
There are two solutions:
splitStatements: false— send the entire SQL block as a single statementendDelimiter— tell Liquibase to split on a different delimiter instead of;
Views
Views are the simplest stored object. The view body does not contain semicolons (it is a single SELECT statement), so splitStatements is not a concern. The primary pattern is CREATE OR REPLACE VIEW so the view can be updated in place without a drop-and-recreate.
Creating a view with runOnChange
# db/changelog/migrations/2026/06/20260610-001-user-order-summary-view.yaml
databaseChangeLog:
- changeSet:
id: "20260610-001"
author: abhay
runOnChange: true
comment: User order summary view — updates automatically when changeset is modified
changes:
- createView:
viewName: v_user_order_summary
replaceIfExists: true
selectQuery: >
SELECT
u.id AS user_id,
u.email,
u.full_name,
COUNT(o.id) AS total_orders,
SUM(o.total) AS lifetime_value,
MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email, u.full_name
rollback:
- dropView:
viewName: v_user_order_summary
runOnChange: true is the key attribute for stored objects. When it is set:
- First run: changeset is applied and its MD5 hash stored in
DATABASECHANGELOG - Subsequent runs with no change: changeset is skipped (hash matches)
- Subsequent runs after the changeset is modified: changeset is re-applied (hash changed)
This means you can update the view definition by editing the changeset directly — no new changeset ID needed. Liquibase detects the change and reruns it.
Important: runOnChange: true is appropriate only for replaceable objects — views, stored procedures, functions, triggers. Never use it on DDL changesets (table creation, column changes) where re-running would fail or cause unintended effects.
replaceIfExists: true
replaceIfExists: true on createView generates CREATE OR REPLACE VIEW instead of CREATE VIEW. Without it, Liquibase generates CREATE VIEW, which fails if the view already exists (for example, after a changeset was re-run via runOnChange).
Always pair createView with both replaceIfExists: true and runOnChange: true.
Stored Procedures
Procedures require special handling because their bodies contain semicolons.
Method 1: splitStatements false (recommended)
# db/changelog/migrations/2026/06/20260611-001-get-user-orders-procedure.yaml
databaseChangeLog:
- changeSet:
id: "20260611-001"
author: abhay
runOnChange: true
comment: Stored procedure to fetch orders with payment status for a user
changes:
- sql:
splitStatements: false
sql: >
CREATE OR REPLACE PROCEDURE get_user_orders(
IN p_user_id BIGINT UNSIGNED,
IN p_status VARCHAR(20)
)
BEGIN
SELECT
o.id,
o.status,
o.total,
o.created_at,
p.method AS payment_method,
p.status AS payment_status
FROM orders o
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.user_id = p_user_id
AND (p_status IS NULL OR o.status = p_status)
ORDER BY o.created_at DESC;
END
rollback:
- sql:
splitStatements: false
sql: DROP PROCEDURE IF EXISTS get_user_orders;
splitStatements: false sends the entire sql block to MySQL as one statement. MySQL receives the full CREATE OR REPLACE PROCEDURE ... END and handles the internal semicolons itself.
The trailing semicolon after END is optional when splitStatements: false — MySQL accepts both END and END; as valid procedure terminators.
Method 2: endDelimiter
An alternative that uses a custom delimiter to separate multiple stored objects in one changeset:
- changeSet:
id: "20260611-002"
author: abhay
runOnChange: true
changes:
- sql:
splitStatements: true
endDelimiter: "//"
sql: |
CREATE OR REPLACE PROCEDURE calculate_order_total(
IN p_order_id BIGINT UNSIGNED,
OUT p_total DECIMAL(12,2)
)
BEGIN
SELECT SUM(quantity * unit_price)
INTO p_total
FROM order_items
WHERE order_id = p_order_id;
END//
CREATE OR REPLACE PROCEDURE apply_coupon(
IN p_order_id BIGINT UNSIGNED,
IN p_coupon_code VARCHAR(50)
)
BEGIN
DECLARE v_discount DECIMAL(8,2);
SELECT discount_value INTO v_discount
FROM coupons
WHERE code = p_coupon_code AND expires_at > NOW();
UPDATE orders
SET total = total - v_discount
WHERE id = p_order_id;
END//
rollback:
- sql:
sql: |
DROP PROCEDURE IF EXISTS calculate_order_total;
DROP PROCEDURE IF EXISTS apply_coupon;
With endDelimiter: "//", Liquibase splits on // instead of ;. The internal semicolons inside each procedure body are left intact. MySQL receives each procedure definition as a complete statement.
Method 1 vs Method 2:
splitStatements: falseis simpler and less error-prone — use it when each changeset contains one stored objectendDelimiteris better when a single changeset needs to create multiple stored objects in sequence- In practice, one stored object per changeset is the cleaner approach — it gives precise rollback and cleaner git history
External SQL files
For long procedures, embedding SQL in YAML is awkward. Use sqlFile to reference an external .sql file:
- changeSet:
id: "20260611-003"
author: abhay
runOnChange: true
comment: Complex order analytics procedure — defined in external SQL file
changes:
- sqlFile:
path: db/procedures/sp_order_analytics.sql
splitStatements: false
stripComments: false
rollback:
- sql:
sql: DROP PROCEDURE IF EXISTS sp_order_analytics;
-- db/procedures/sp_order_analytics.sql
CREATE OR REPLACE PROCEDURE sp_order_analytics(
IN p_start_date DATE,
IN p_end_date DATE
)
BEGIN
-- Revenue by day
SELECT
DATE(created_at) AS order_date,
COUNT(*) AS order_count,
SUM(total) AS daily_revenue
FROM orders
WHERE created_at BETWEEN p_start_date AND p_end_date
AND status = 'delivered'
GROUP BY DATE(created_at)
ORDER BY order_date;
END
stripComments: false preserves SQL comments in the file. By default, Liquibase strips comments before sending SQL — which can cause problems with DELIMITER-style syntax and can obscure intent in the database’s stored procedure list.
Directory structure for external SQL:
src/main/resources/db/
├── changelog/
│ └── migrations/
│ └── 2026/06/
│ └── 20260611-003-sp-order-analytics.yaml
└── procedures/
└── sp_order_analytics.sql
Triggers
Triggers are the most complex stored object because:
- They use semicolons internally (like procedures)
- MySQL requires
DELIMITERcommands in interactive clients, but Liquibase sends SQL directly to the JDBC driver without a client - There is no
CREATE OR REPLACE TRIGGERin MySQL before 8.0.19 — you mustDROP TRIGGER IF EXISTSthenCREATE TRIGGER
Creating a trigger
# db/changelog/migrations/2026/06/20260612-001-users-audit-trigger.yaml
databaseChangeLog:
- changeSet:
id: "20260612-001"
author: abhay
runOnChange: true
comment: Audit trigger — log updates to users table
changes:
- sql:
splitStatements: false
sql: >
DROP TRIGGER IF EXISTS trg_users_after_update;
CREATE TRIGGER trg_users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
INSERT INTO audit_log (
table_name,
record_id,
action,
changed_by,
changed_at,
old_values,
new_values
) VALUES (
'users',
NEW.id,
'UPDATE',
CURRENT_USER(),
NOW(),
JSON_OBJECT(
'email', OLD.email,
'status', OLD.status,
'role', OLD.role
),
JSON_OBJECT(
'email', NEW.email,
'status', NEW.status,
'role', NEW.role
)
);
END
rollback:
- sql:
sql: DROP TRIGGER IF EXISTS trg_users_after_update;
The pattern DROP TRIGGER IF EXISTS followed immediately by CREATE TRIGGER inside a single splitStatements: false block handles:
- First run: no existing trigger,
DROPis a no-op,CREATEsucceeds - Re-run via
runOnChange: drops the existing trigger, creates the updated version - Idempotency: safe to run multiple times
Why not CREATE OR REPLACE TRIGGER? MySQL 8.0.19+ supports CREATE OR REPLACE TRIGGER, but older 8.x patch versions do not. Using DROP IF EXISTS + CREATE works across all MySQL 8.x versions.
The audit_log table prerequisite
The trigger above references an audit_log table. Ensure that table exists before this changeset runs — use a precondition:
preConditions:
onFail: HALT
onFailMessage: "audit_log table must exist before creating the users audit trigger."
tableExists:
tableName: audit_log
Managing Multiple Versions of a Stored Object
With runOnChange: true, you edit the changeset in place when the procedure or view changes. But how does this work with the immutability rule from Part 1?
The exception: runOnChange: true explicitly opts out of the immutability contract. Liquibase re-applies the changeset when its content changes, updating DATABASECHANGELOG with the new MD5 hash. This is intentional for stored objects because:
- Their bodies evolve frequently
- The
CREATE OR REPLACE/DROP + CREATEpattern handles re-application gracefully - Creating a new changeset ID for every procedure edit would result in thousands of revision changesets
What git blame becomes: You track the history of a view definition through git history of the changeset file, not through DATABASECHANGELOG. The DATABASECHANGELOG row just shows when it last ran, not every version.
Organizing Stored Objects
Use a dedicated directory for stored object definitions, separate from migration files:
db/
├── changelog/
│ ├── db.changelog-master.yaml
│ └── migrations/
│ └── 2026/06/
│ ├── 20260610-001-user-order-summary-view.yaml ← view changeset
│ ├── 20260611-001-get-user-orders-procedure.yaml ← procedure changeset
│ └── 20260612-001-users-audit-trigger.yaml ← trigger changeset
└── procedures/
│ └── sp_order_analytics.sql
└── views/
└── (for complex views using sqlFile)
The changeset YAML files stay in migrations/ (picked up by includeAll). The .sql body files live in db/procedures/ or db/views/, referenced via sqlFile path:.
Common Mistakes
Using splitStatements: true (the default) with procedure bodies: Liquibase splits on ;, sending fragments of your procedure to MySQL. The first fragment (CREATE PROCEDURE ... BEGIN) is invalid SQL and MySQL errors immediately. Always set splitStatements: false for procedures, functions, and triggers.
Omitting runOnChange: true on views and procedures: Without it, editing the changeset after it has been applied raises a checksum mismatch error. You either have to clearCheckSums (risky) or create a new changeset with a DROP + CREATE — which is exactly what runOnChange + CREATE OR REPLACE handles automatically.
Using DELIMITER $$ syntax in the SQL block: DELIMITER is a MySQL CLI command, not SQL. The JDBC driver does not understand it. Liquibase sends SQL directly to JDBC — DELIMITER in your changeset will cause a syntax error. Use splitStatements: false or endDelimiter instead.
Best Practices
splitStatements: false+CREATE OR REPLACE+runOnChange: true— the standard triple for every view, procedure, function, and trigger- One stored object per changeset — precise rollback, clean git history, easier code review
DROP TRIGGER IF EXISTSbeforeCREATE TRIGGER— MySQL’s lack ofOR REPLACEfor triggers (pre-8.0.19) requires explicit drop; both in onesplitStatements: falseblock- External
.sqlfiles viasqlFilefor long procedures — YAML embedding becomes unreadable beyond ~20 lines stripComments: falseonsqlFile— preserve comments in complex SQL for future maintainerstableExistsprecondition on triggers that reference other tables — fails clearly rather than with a cryptic FK error
What You’ve Learned
- Stored object bodies contain semicolons that conflict with Liquibase’s default statement splitting — fix with
splitStatements: falseorendDelimiter runOnChange: trueopts a changeset out of the immutability rule — re-applies the changeset when its content changes- Views: use
createViewwithreplaceIfExists: trueandrunOnChange: true - Procedures: use
sqlorsqlFilewithsplitStatements: falseandCREATE OR REPLACE - Triggers: use
DROP TRIGGER IF EXISTS+CREATE TRIGGERin onesplitStatements: falseblock DELIMITERis a MySQL CLI command — it is not valid in Liquibase SQL; usesplitStatementsandendDelimiterinstead- External
.sqlfiles viasqlFilekeep long procedure definitions readable
Next: Article 14 — MySQL-Specific Patterns: Character Sets, Engines, Large Table Migrations — handling MySQL’s unique DDL constraints for production workloads.