Stored Procedures and Triggers

Stored Procedures

A stored procedure is SQL logic saved inside the database that you can call by name. It centralises business rules and reduces traffic between app and DB.

DELIMITER $$
CREATE PROCEDURE GetCustomersByCity(IN city_name VARCHAR(80))
BEGIN
  SELECT * FROM customers WHERE city = city_name;
END$$
DELIMITER ;

CALL GetCustomersByCity('Mumbai');

Stored Functions

CREATE FUNCTION Discounted(total DECIMAL(10,2))
RETURNS DECIMAL(10,2) DETERMINISTIC
RETURN total * 0.9;

SELECT Discounted(100.00);   -- 90.00

Triggers

Triggers run automatically when an event happens on a table.

CREATE TRIGGER audit_customer_insert
AFTER INSERT ON customers
FOR EACH ROW
INSERT INTO audit_log(what, who) VALUES ('new customer', NEW.name);

Use Cases for Triggers

• Audit trails: record who changed what and when.
• Validation or derived-value maintenance.
• Syncing related tables automatically.

Key Points

• Procedures group reusable SQL; CALL runs them.
• Functions return a single value and can be used in SELECT.
• Triggers fire before or after INSERT, UPDATE, DELETE.
• Use DELIMITER $$ in the client so semicolons inside the body are not executed early.