Stored Procedures, Functions, and Triggers

Stored Procedures

• A stored procedure is a saved block of SQL code that performs an action.

• It can:

  • Change data (INSERT, UPDATE, DELETE).

  • Use transactions.

  • Return 0 or more results.

  • Accept parameters.

  • Be called by applications and scheduled jobs.

  • Unlike functions, procedures DO NOT return values but can use OUT parameters.

Creating a Procedure

-- Create a procedure to increase a product’s price by X%
DELIMITER $$

CREATE PROCEDURE increase_price(
    IN product_id INT,
    IN percent DECIMAL(5,2)
)
BEGIN
    UPDATE products
    SET price = price * (1 + (percent / 100))
    WHERE id = product_id;
END$$

DELIMITER ;

-- Call the procedure
CALL increase_price(3, 10);

User-Defined Functions (UDFs)

• A function returns a value and can be used in:

  • SELECT statements

  • WHERE clauses

  • JOINs

  • Computations

• Functions cannot perform transactional commits. They are intended for calculations, transformations, and queries.

Create a Function

-- Create a function that returns full name
DELIMITER $$

CREATE FUNCTION get_full_name(first_name TEXT, last_name TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
    RETURN CONCAT(first_name, ' ', last_name);
END$$

DELIMITER ;

-- Call the function
SELECT get_full_name('John', 'Doe');

Create a Function

-- Scalar Function for Discounted Price
DELIMITER $$

CREATE FUNCTION calculate_discount(price DECIMAL(10,2), pct INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price - (price * pct / 100);
END$$

DELIMITER ;

-- Use it inside a SELECT:
SELECT name, calculate_discount(price, 15)
FROM products;

tip

• A Scalar Function is a function that returns one value for each call, such as a text string, number, or computed result.

IN, OUT, INOUT Parameters

• Functions and procedures can accept different types of parameters that control how values flow in and out.

IN Parameters

• Default type.

= Value is passed into the procedure / function.

• Cannot be modified and returned automatically.

-- Procedure that uses IN parameters
DELIMITER $$

CREATE PROCEDURE apply_tax(
    IN product_id INT,
    IN tax_pct DECIMAL(5,2)
)
BEGIN
    UPDATE products
    SET price = price * (1 + tax_pct / 100)
    WHERE id = product_id;
END$$

DELIMITER ;

-- Call the procedure
CALL apply_tax(10, 12.5);

OUT Parameters

• Procedure / function returns values through OUT variables.

• No need for RETURN statement in procedures.

-- Return a product’s name and price using a procedure
DELIMITER $$

CREATE PROCEDURE get_product_info(
    IN product_id INT,
    OUT product_name VARCHAR(255),
    OUT product_price DECIMAL(10,2)
)
BEGIN
    SELECT name, price
    INTO product_name, product_price
    FROM products
    WHERE id = product_id;
END$$

DELIMITER ;

-- Call the procedure
CALL get_product_info(5, @pname, @pprice);
SELECT @pname AS product_name, @pprice AS product_price;

INOUT Parameters

• Parameter is passed in, can be modified, and returned back.

DELIMITER $$

CREATE PROCEDURE apply_discount(
    INOUT price DECIMAL(10,2),
    IN discount_pct DECIMAL(5,2)
)
BEGIN
    SET price = price - (price * discount_pct / 100);
END$$

DELIMITER ;

-- Call the procedure
SET @p = 200;
CALL apply_discount(@p, 20);
SELECT @p AS discounted_price;  -- returns 160

Summary

Parameter	Direction	Modifiable?	Returned?
IN	Input	No	No
OUT	Output	Yes	Yes
INOUT	Input/Output	Yes	Yes

TRIGGERS (Basics)

• A trigger is an automatic action that runs when a row is INSERTED, UPDATED, and DELETED.

• Uses:

  • Auditing.

  • Enforcing business rules.

  • Maintaining derived data (Counters and logs).

  • Validating inputs.

Logging Updates

CREATE TABLE product_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    old_price DECIMAL(10,2),
    new_price DECIMAL(10,2),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create a trigger
DELIMITER $$

CREATE TRIGGER price_change_trigger
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.price <> OLD.price THEN
        INSERT INTO product_logs (product_id, old_price, new_price)
        VALUES (OLD.id, OLD.price, NEW.price);
    END IF;
END$$

DELIMITER ;

-- An audit log is AUTOMATICALLY inserted into product_logs.
UPDATE products SET price = 200 WHERE id = 5;

Summary Table

Feature	Purpose	Can Modify Data?	Returns Value?	Used In SELECT?
Stored Procedure	Execute business logic	Yes	No	No
Function (User Defined Function)	Compute and return values	No	Yes	Yes
Trigger	Auto-run on data changes	N/A	N/A	No