Does a stored procedure automatically wrap its statements in one transaction?

No. Each statement inside a procedure commits independently under autocommit, exactly like statements run outside one, unless the procedure body explicitly issues START TRANSACTION and COMMIT. The place_order procedure here relies on the SIGNAL check happening before any INSERT or UPDATE runs, so the failure path never touches data — it doesn't rely on transactional rollback.

Why does the procedure use SELECT ... FOR UPDATE instead of a plain SELECT?

A plain SELECT lets two concurrent calls both read the same stock value, both pass the check, and both proceed to oversell — the same race this site's locking-and-deadlocks lesson demonstrates. FOR UPDATE locks the row being checked so a second concurrent call to place_order for the same book waits until the first one finishes, instead of reading stale stock.

Can a trigger see which statement or procedure caused the change it's reacting to?

No — a trigger only sees the row's OLD and NEW values, not the calling context. That's exactly what log_stock_change demonstrates: it logs identical rows whether the change came from place_order or from a raw UPDATE typed directly into a client, because the trigger has no way to tell them apart.

What happens if a trigger's own logic raises an error?

The triggering statement is aborted along with the trigger's own work — an AFTER UPDATE trigger that fails effectively vetoes an update that had already appeared to succeed until the trigger ran, which is why trigger bodies are usually kept simple and side-effect-focused rather than validation-heavy.

How do you see what triggers exist on a table without opening every CREATE script?

SHOW TRIGGERS FROM LIKE ' ' lists every trigger on that table along with its timing, event, and full body — the same information SHOW CREATE PROCEDURE gives for a stored procedure, so neither requires re-reading migration history to find out what currently runs.

Stored Procedures and Triggers: Code That Runs Without Being Asked

A syntax tutorial shows the CREATE PROCEDURE and CREATE TRIGGER statements. It doesn’t show what a caller can’t see once they run: a procedure that refuses to do part of its job, and a trigger that fires for changes the procedure never made.

A procedure that enforces its own rule

DELIMITER //

CREATE PROCEDURE place_order(IN p_book_id INT, IN p_qty INT)
BEGIN
    DECLARE current_stock INT;

    SELECT stock INTO current_stock FROM books WHERE id = p_book_id FOR UPDATE;

    IF current_stock < p_qty THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Not enough stock to fulfill this order';
    END IF;

    INSERT INTO order_items (book_id, quantity) VALUES (p_book_id, p_qty);
    UPDATE books SET stock = stock - p_qty WHERE id = p_book_id;
END //

DELIMITER ;

CALL place_order(1, 5);
SELECT * FROM books WHERE id = 1;
SELECT * FROM order_items;

id  title  genre  stock
1   Dune   SciFi  45

id  book_id  quantity
1   1        5

Book 1 started at 50 in stock; ordering 5 dropped it to 45 and left a matching row in order_items — both changes made by a single CALL, not by the client running two separate statements it has to keep in sync itself.

The failure path: a real error, not a silent no-op

CALL place_order(2, 999);

ERROR 1644 (45000) at line 1: Not enough stock to fulfill this order

-- checked immediately after the error
SELECT * FROM books WHERE id = 2;
SELECT * FROM order_items;

id  title       genre  stock
2   Foundation  SciFi  30

id  book_id  quantity
1   1        5

Book 2’s stock is untouched and no new order_items row exists — the SIGNAL runs before the INSERT or UPDATE statements, so the failure path never reaches the writes at all. The error text is exactly what MESSAGE_TEXT set, and SQLSTATE '45000' is the standard code for a user-defined condition, which is why the client sees ERROR 1644 (45000) rather than a generic MySQL error number.

A trigger that fires whether or not the procedure was involved

CREATE TRIGGER log_stock_change
AFTER UPDATE ON books
FOR EACH ROW
BEGIN
    IF NEW.stock <> OLD.stock THEN
        INSERT INTO stock_log (book_id, change_amount, reason)
        VALUES (NEW.id, NEW.stock - OLD.stock, 'stock update');
    END IF;
END;

-- change 1: through the procedure
CALL place_order(3, 4);

-- change 2: a raw UPDATE that never calls place_order at all
UPDATE books SET stock = stock + 100 WHERE id = 3;

SELECT * FROM stock_log;

id  book_id  change_amount  reason        logged_at
1   3        -4             stock update  2026-09-13 17:58:04
2   3        100            stock update  2026-09-13 17:58:10

Both rows exist, and they’re indistinguishable in shape — the trigger logged the raw UPDATE exactly as faithfully as the procedure-driven change, because AFTER UPDATE ON books fires for every update to the table, regardless of what code path produced it. A syntax reference lists CREATE TRIGGER’s grammar; it doesn’t make this visible: nothing in the client’s own code called any logging function for that second change, and the log entry appeared anyway.

Practice

1. Update a book's stock to the same value it already has, and confirm no log entry appears.

UPDATE books SET stock = stock WHERE id = 3;
SELECT COUNT(*) AS log_rows FROM stock_log;

log_rows
2

Still 2 rows, not 3 — the trigger’s IF NEW.stock <> OLD.stock guard skips the insert when the value didn’t actually change, even though AFTER UPDATE still fires for the no-op update itself. Without that guard, every UPDATE statement touching the row would produce a log entry regardless of whether anything moved.

2. List every trigger currently defined on the books table.

SHOW TRIGGERS FROM bookshop LIKE 'books';

Trigger            Event   Table  Timing  ...
log_stock_change   UPDATE  books  AFTER   ...

One row per trigger, showing its event (UPDATE), timing (AFTER), and full body — this is the fastest way to find out what fires on a table without searching migration history for the original CREATE TRIGGER statement.

3. Inspect the full definition of the place_order procedure.

SHOW CREATE PROCEDURE place_order\G

*************************** 1. row ***************************
           Procedure: place_order
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `place_order`(IN p_book_id INT, IN p_qty INT)
BEGIN
    DECLARE current_stock INT;
    SELECT stock INTO current_stock FROM books WHERE id = p_book_id FOR UPDATE;
    IF current_stock < p_qty THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Not enough stock to fulfill this order';
    END IF;
    INSERT INTO order_items (book_id, quantity) VALUES (p_book_id, p_qty);
    UPDATE books SET stock = stock - p_qty WHERE id = p_book_id;
END

SHOW CREATE PROCEDURE returns the exact body MySQL has stored and will execute — useful for confirming a deployed procedure actually matches what’s in source control, rather than trusting that a migration ran cleanly.

4. Explain why removing FOR UPDATE from the SELECT INTO would reintroduce the overselling race from the locking lesson.

Without FOR UPDATE, two concurrent calls to place_order for the same book both run their SELECT stock INTO current_stock before either has written anything back. If stock is 5 and both calls check “is 5 < requested quantity,” both can pass the check for a quantity that only one of them should be able to fulfill — the same read-then-write race the locking-and-deadlocks lesson demonstrated with accounts.balance, just moved into a procedure instead of hand-written session SQL. FOR UPDATE closes it by making the second call’s SELECT block until the first call’s transaction finishes, so it sees the already-decremented stock.

Next: replication — copying a database’s changes to a second server in real time, and what actually shows up when you check whether it kept up.

A procedure that enforces its own rule

The failure path: a real error, not a silent no-op

A trigger that fires whether or not the procedure was involved

Practice

Frequently Asked Questions