MariaDB / Advanced: evidence and reliable operations / Lesson 17
Stored routines, triggers and events
Use stored code where its execution and privilege boundaries are clear. Distinguish functions, procedures, row triggers and scheduled events.
On this page
Lesson goals
- Explain routine control flow and signaling.
- Review transaction ownership and hidden trigger effects.
Choose the right stored object
A procedure is invoked with CALL and can coordinate statement sequences. A stored function returns a scalar and is used in expressions, with restrictions on what it may do. A trigger runs automatically for each affected row on its table; an event runs on a schedule when the event scheduler and privileges permit it. Hidden execution can simplify a local invariant but also surprises callers, imports and replication. Document the owner, privilege context and business responsibility of each object.
Signal a clear validation error
This procedure validates a fee and returns a calculated amount without changing a table. DELIMITER is a mariadb client directive used so internal semicolons are sent as one definition; an API normally sends CREATE PROCEDURE as one string without that directive. SQL SECURITY INVOKER makes the caller context explicit. Calling quote_fee with 10 returns 11.00, while a negative or NULL input raises the custom SQLSTATE 45000 message. Real tax calculations need a separate documented rounding policy.
DELIMITER //
CREATE PROCEDURE quote_fee(IN p_fee DECIMAL(9,2))
SQL SECURITY INVOKER
BEGIN
IF p_fee IS NULL OR p_fee < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'fee must be nonnegative';
END IF;
SELECT CAST(p_fee * 1.10 AS DECIMAL(10,2)) AS quoted_fee;
END//
DELIMITER ;
CALL quote_fee(10);quoted_fee
11.00Expected successful result; the custom error text is intentionally stable and untranslated.
Decide who owns the transaction
A procedure can contain transaction control in supported contexts, but calling a procedure that commits can unexpectedly end work started by its caller. MariaDB does not provide ordinary nested transactions through repeated START TRANSACTION. Prefer a clear contract: either the caller owns the transaction or a top-level procedure owns the whole business unit. Stored functions and triggers cannot commit or roll back independently. Exception handlers must preserve failure semantics rather than swallowing an error and returning apparent success.
Inspect code before blaming a simple UPDATE
A single row update can activate triggers that write audit rows or validate state. Inventory those dependencies during debugging and migration. The read-only queries list visible routines and triggers in the training schema, but metadata visibility is privilege-limited. SHOW CREATE PROCEDURE or SHOW CREATE TRIGGER can reveal effective definitions when authorized. On transactional tables, trigger effects participate in the invoking statement transaction; mixing nontransactional tables can undermine an assumed all-or-nothing audit design.
SELECT ROUTINE_NAME, SECURITY_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'harbor_lab' ORDER BY ROUTINE_NAME;
SELECT TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'harbor_lab' ORDER BY TRIGGER_NAME;Metadata absence may reflect permissions; it is not proof that no stored code exists.
Scheduled work needs lifecycle control
An event is not guaranteed to run merely because its definition exists: scheduler state, definer validity and privileges matter. Restoring a backup into a test environment can accidentally activate scheduled work if the environment is not fenced. Review event enablement, time-zone interpretation, overlap and retry behavior before deployment. Store routine definitions in version-controlled migrations, compare effective definitions after restore, and test the exact MariaDB SQL mode because mode at creation can affect stored-code behavior.
Common errors and fixes
ERROR 1064 near DELIMITER in an API call
- Why it happens
- A client directive was sent as server SQL.
- How to fix it
- Send the routine definition as one API statement without DELIMITER lines.
- Prevent it next time
- Separate command-line scripting conventions from connector behavior.
An inner routine commits unrelated caller work
- Why it happens
- Transaction ownership was not part of the procedure contract.
- How to fix it
- Refactor boundaries so one layer owns commit and rollback.
- Prevent it next time
- Document transaction behavior in every callable routine interface.
ERROR 1449: The user specified as a definer does not exist
- Why it happens
- A restored stored object refers to an absent account.
- How to fix it
- Review and recreate ownership/security context through an authorized migration.
- Prevent it next time
- Inventory definers and required grants in backup manifests.
Check your understanding
1. What should CALL quote_fee(NULL) do, and why is checking only p_fee < 0 insufficient?
Show worked solution
It should raise SQLSTATE 45000 with fee must be nonnegative. A comparison of NULL with zero is unknown, so a negative-only IF may not enter its rejection branch. The explicit IS NULL condition closes that gap. Verify both invalid cases and the exact decimal output for valid boundary values.
2. A backup restore passes table checks but its nightly event sends duplicate exports. Which design boundary was missed?
Show worked solution
The restore environment was not isolated from scheduled business effects. Inventory events, scheduler policy and outbound credentials before startup, then verify that restored test services cannot contact production destinations. Design exports with explicit run identity and idempotency. A structurally valid database is not automatically safe to run as a second active application environment.
Key takeaways
- Stored code needs explicit privilege, transaction and deployment contracts.
- Automatic execution belongs in backup and migration inventories.