MariaDB Tutorial / Use Stored Procedures, Functions, and Triggers

A stored procedure is an explicit server-side operation invoked with CALL, a stored function returns a value inside a SQL expression, and a trigger runs automatically in response to a table change. Their invocation, privileges, and transaction responsibilities differ even though all three store SQL logic on the server.

Choose an object by its role

Procedures are not required to modify data, and stored functions are not universally limited to arithmetic. This lab makes the differences concrete: a procedure changes stock, a function calculates an amount, and a trigger records old and new stock. MariaDB triggers run for each row, so a statement changing many rows also incurs the corresponding trigger work. Compare caller-controlled commit/rollback in Start InnoDB Transactions with COMMIT and ROLLBACK and the stored-view execution context in Save Reusable Query Definitions with VIEW to distinguish these objects.

Hidden server-side behavior can complicate troubleshooting. Distinguish an operation the application calls deliberately from logic triggered by every matching table write. Decide whether a rule belongs in application validation or must be enforced across several write paths before moving it into the database.

Prepare privileges and the two InnoDB tables

Use a dedicated appops_lab schema and a lab account with CREATE ROUTINE, TRIGGER, and the required table privileges. Calling routines also requires EXECUTE. Review accounts and grants rather than granting global administrator rights to an application. Binary-log policies can add requirements for creating functions; have the DBA check that policy instead of weakening server-wide trust settings merely to make the lab succeed.

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE routine_stock (
  product_id INT NOT NULL PRIMARY KEY,
  stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB;
CREATE TABLE routine_audit (
  audit_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  old_stock INT NOT NULL,
  new_stock INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO routine_stock VALUES (1, 8);

Both tables use the transactional InnoDB engine. That condition allows the stock update and the trigger’s audit insertion to roll back together. If an exercise object already exists, inspect its definition and data before repeating creation; the setup intentionally does not drop existing objects.

Create all three objects with complete definitions

The following script uses the mariadb command-line client’s syntax. DELIMITER configures how that client recognizes the end of a statement; it is not a server SQL statement. Use the script-execution feature of a graphical editor. With a Python driver, send each complete CREATE statement once without DELIMITER lines or the // terminators.

DELIMITER //
CREATE FUNCTION line_total(p_price DECIMAL(10,2), p_qty INT)
RETURNS DECIMAL(12,2)
DETERMINISTIC NO SQL SQL SECURITY INVOKER
BEGIN
  IF p_price IS NULL OR p_qty IS NULL OR p_price < 0 OR p_qty < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonnegative price and quantity required';
  END IF;
  RETURN ROUND(p_price * p_qty, 2);
END//

CREATE PROCEDURE adjust_stock(IN p_product_id INT, IN p_delta INT)
SQL SECURITY INVOKER
MODIFIES SQL DATA
BEGIN
  IF p_delta IS NULL OR p_delta = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonzero stock delta required';
  END IF;
  UPDATE routine_stock SET stock = stock + p_delta
  WHERE product_id = p_product_id AND stock + p_delta >= 0;
  IF ROW_COUNT() <> 1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Missing product or insufficient stock';
  END IF;
END//

CREATE TRIGGER audit_stock_update
AFTER UPDATE ON routine_stock FOR EACH ROW
BEGIN
  IF OLD.stock <> NEW.stock THEN
    INSERT INTO routine_audit (product_id, old_stock, new_stock)
    VALUES (NEW.product_id, OLD.stock, NEW.stock);
  END IF;
END//
DELIMITER ;

line_total rejects negative or NULL inputs and returns a two-decimal amount. Reassess its DECIMAL range for large quantities or prices. The declarations DETERMINISTIC NO SQL describe this particular implementation and must remain truthful if the implementation changes. The CREATE FUNCTION reference explains the characteristics and security context.

adjust_stock updates exactly one existing product only when the resulting stock is nonnegative. Otherwise it raises a business error with SIGNAL. Read ROW_COUNT() immediately after the UPDATE; inserting another statement can change which operation it describes. The CREATE PROCEDURE reference covers parameters and invocation security.

The trigger inserts an audit row only when stock actually changes. This is a change-recording exercise, not an independent tamper-resistant audit system. Refer to CREATE TRIGGER for OLD and NEW row values and trigger timing.

Compare both tables across rollback and commit

SELECT line_total(12.50, 3) AS amount;
START TRANSACTION;
CALL adjust_stock(1, -3);
SELECT product_id, stock FROM routine_stock;
SELECT product_id, old_stock, new_stock FROM routine_audit;
ROLLBACK;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT COUNT(*) AS audit_rows FROM routine_audit;

The amount is 37.50. Inside the transaction, stock becomes five and the audit row records eight becoming five. After ROLLBACK, stock must be eight again and the audit row count must be zero. An auto-increment identifier need not be reused after a rollback; gaps in audit IDs are not evidence of a failed rollback.

START TRANSACTION;
CALL adjust_stock(1, -2);
COMMIT;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT old_stock, new_stock FROM routine_audit ORDER BY audit_id;

After this commit, stock is six and one audit row records eight becoming six. The procedure deliberately has no internal COMMIT, allowing the caller to combine it with other work. A trigger runs within the triggering statement’s transaction context and does not independently commit. Review InnoDB transactions, COMMIT, and ROLLBACK when interpreting these results.

Observe invalid inputs and privilege boundaries

Execute these statements separately. The first attempts to subtract more than the current stock of six; the second supplies a negative unit price. Both should raise SQLSTATE 45000. Verify afterward that stock remains six and the audit table still has its one committed row.

CALL adjust_stock(1, -99);
SELECT line_total(-1, 2);

The function and procedure declare SQL SECURITY INVOKER, so table access uses the caller’s privileges. An account granted EXECUTE but not UPDATE cannot modify stock through this procedure. A trigger instead uses its DEFINER’s security context; it is not declared INVOKER. Manage the trigger definer, account lifecycle, and required table access accordingly.

The trigger limitations restrict actions such as modifying the triggering table again and controlling transactions. Keep trigger work short and relevant to the database transaction rather than hiding long external operations behind a row change.

Inspect definitions and remove only disposable objects

SHOW CREATE PROCEDURE adjust_stock;
SHOW CREATE FUNCTION line_total;
SHOW TRIGGERS FROM appops_lab;

Record definitions and security contexts, and confirm that backup and restore include the required routines and triggers. A definition restored onto another server can still fail if its DEFINER account is absent or lacks privileges. When this isolated lab is finished, remove only its own objects:

DROP TRIGGER appops_lab.audit_stock_update;
DROP PROCEDURE appops_lab.adjust_stock;
DROP FUNCTION appops_lab.line_total;
DROP TABLE appops_lab.routine_audit;
DROP TABLE appops_lab.routine_stock;

An ordinary ROLLBACK does not recover dropped objects. Confirm that the names identify disposable lab objects. Changing production routines also requires coordinating callers and a deployment and recovery process.