SQL Stored Procedures, Functions, Triggers and Transactions Explained

As you advance in SQL, understanding procedures, functions, triggers, and transactions becomes essential for database automation and integrity. This article breaks down these concepts step-by-step.

---

Part 1: SQL Stored Procedures

Stored procedures are precompiled SQL statements saved in the database that can be executed repeatedly.

Syntax

CREATE PROCEDURE procedure_name()
BEGIN
  -- SQL statements
END;

Example

DELIMITER //
CREATE PROCEDURE GetAllCustomers()
BEGIN
  SELECT * FROM Customers;
END //
DELIMITER ;

Call it using:

CALL GetAllCustomers();

Part 2: SQL Functions

Functions are similar to procedures but return a single value and can be used in SQL expressions.

Syntax

CREATE FUNCTION function_name (param1 datatype)
RETURNS return_datatype
BEGIN
  -- SQL logic
  RETURN value;
END;

Example

CREATE FUNCTION GetTotalPrice(quantity INT, price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
BEGIN
  RETURN quantity * price;
END;

Use like this:

SELECT GetTotalPrice(10, 5.50);

---

Part 3: SQL Triggers

Triggers are automatic actions fired by events such as INSERT, UPDATE, or DELETE.

Syntax

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
  -- SQL logic
END;

Example

CREATE TRIGGER after_insert_customer
AFTER INSERT ON Customers
FOR EACH ROW
BEGIN
  INSERT INTO LogTable(action, log_time)
  VALUES('Customer Added', NOW());
END;

This automatically logs whenever a new customer is added.

Part 4: SQL Transactions

SQL transactions group multiple SQL operations into a single unit that can be committed or rolled back together.

Keywords

• BEGIN – starts a transaction
• COMMIT – saves all changes
• ROLLBACK – undoes all changes

Example

START TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

If any step fails, use:

ROLLBACK;

---

Best Practices

• Use transactions for any operation that changes multiple rows/tables.
• Validate inputs before using procedures or triggers.
• Keep triggers minimal to avoid performance overhead.

Also Read:

• SQL Constraints, Indexes, and Views
• SQL Joins and Subqueries
• SQL Clauses and Operators

FAQs

• Q: What’s the difference between a procedure and a function?
  A: Functions return a value and can be used in queries. Procedures perform actions and cannot return values directly.
• Q: When should I use transactions?
  A: Whenever multiple queries need to succeed or fail together.
• Q: Do all databases support triggers?
  A: Most major databases do, including MySQL, PostgreSQL, and SQL Server.

Explore more SQL tutorials on Sabbir93s Blog.