Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › SQL

Views, stored procedures, and triggers

Why did the stock go down when you ran one INSERT line?

A database can store more than tables. A view is a stored SELECT statement, a stored procedure is a set of statements that you call by name, and a trigger is a statement that the database runs automatically when an INSERT or UPDATE happens.

Checked against real execution · The same view, procedure, and trigger were created on MySQL 8.4, and orders for all 12 combinations were run and checked against the screen

Concepts in this lab

View
A SELECT statement that is saved under a name so that you can query it like a table. It does not store data separately. The defined query runs each time you query it. Use it to make complex queries simpler to write or to restrict access by showing only some columns.
Stored procedure
This is a set of SQL statements that is given a name and stored inside the database. The application only has to call it with CALL name(values), and the statements inside run one after another on the database server. Several statements can be handled in one call. But the business rules then live in the database rather than in the application code, so you cannot tell the behavior just by reading the code, and version control and testing become harder. The statements inside a procedure do not automatically become one transaction, so if they must succeed together or be rolled back together, you have to group them in a transaction.
Trigger
This is a statement that you attach to a table so that the database activates it automatically when an INSERT, UPDATE, or DELETE happens. You create it by choosing the table, the kind of change, and whether it runs before (BEFORE) or after (AFTER) the change, and inside it you can read the values of the row being changed with NEW and OLD. You can hand over processing that must not be forgotten, such as recording changes, but nothing about it shows up in the code of whoever sent the statement. That can make it hard to find out why another table changed or why an error occurred, and if a trigger fails, the original statement that activated it fails too.
CHECK constraint
This is a constraint, written in the table definition, that states a condition the values of a column must meet. For example, if you define CHECK (stock >= 0), an INSERT or UPDATE that would make the stock negative is rejected with an error. Its advantage is that, whichever program changes the value, the database stops it as a last line of defense. MySQL actually enforces this constraint from 8.0.16, and earlier versions accepted the syntax but did not check it.
Transaction
A way to group several data changes into a single unit so that either all of them are applied (commit) or all of them are canceled (rollback). In a bank transfer, it prevents an intermediate state where only one of the withdrawal and the deposit is applied. It is the most basic means a database has for keeping data consistent.

Try these

Running with the default settings

  1. Setup: The product table has one row for the keyboard, and its stock is 10. The v_stock view and the stock_copy table have been created. The view stores only a SELECT statement, and the copy stores the current result (a stock of 10) as rows.
  2. INSERT INTO orders ... VALUES (1, 3): The application ran only this one statement. One order row (quantity 3) is inserted into orders.
  3. Trigger trg_order_stock: An INSERT happened on orders, so the database activates the trigger automatically. The UPDATE in the trigger reduces the stock from 10 to 7. This UPDATE appears nowhere in the application code.
  4. Second statement of the trigger: A row recording the change is inserted into stock_log, with the memo "trigger". Everything up to here happened inside the one INSERT statement.
  5. SELECT stock FROM v_stock: The view holds no data, only a SELECT statement. It reads product at the moment you query it and returns a stock of 7. The view itself was never changed, but the base table changed, so the result changed too.
  6. SELECT stock FROM stock_copy: The copy table stored the result from when it was created, as its own rows. It does not change when product changes, so it is still 10. This value no longer matches the actual stock.

Result of this run

Code

-- product table: (1, 'keyboard', stock 10)
-- The stock column has a CHECK (stock >= 0) constraint

CREATE VIEW v_stock AS            -- View: stores only the SELECT statement
  SELECT name, stock FROM product;
CREATE TABLE stock_copy AS        -- Copy: stores the current result as rows
  SELECT name, stock FROM product;

CREATE TRIGGER trg_order_stock AFTER INSERT ON orders FOR EACH ROW
BEGIN
  UPDATE product SET stock = stock - NEW.qty WHERE id = NEW.product_id;
  INSERT INTO stock_log (product_id, change_qty, memo)
    VALUES (NEW.product_id, -NEW.qty, 'trigger');
END;

-- The application runs only this one statement
INSERT INTO orders (product_id, qty) VALUES (1, 3);

SELECT stock FROM v_stock;        -- Query through the view
SELECT stock FROM stock_copy;     -- Query the copy

Assumptions of this model

For all 12 combinations, the same tables, view, procedure, and trigger as the ones on the screen were created on MySQL 8.4 (InnoDB, autocommit on) on the PC used to write this lab, and the order statement was run. The error or success, the number of rows in each table, the stock, and the values from the view and the copy were checked against the screen. That the view stores no data was checked in information_schema, where v_stock shows up as a VIEW with no storage engine and stock_copy shows up as an InnoDB table. That the stock returns when the statements are grouped in a transaction and rolled back was also checked by running it once on the same database. That the original statement is rolled back together with a failed trigger is the behavior on an engine that supports transactions, such as InnoDB. How much remains after an error inside a procedure depends on the autocommit setting, START TRANSACTION inside the procedure, and handlers (DECLARE ... HANDLER), and here the simplest procedure without any of these was used. The error when the stock is short was produced by a CHECK constraint (which MySQL actually enforces from 8.0.16).

Previous lab: Offset and keyset pagination · Next lab: Binary search and B-trees

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these