4.SQL Data Manipulation & Transactions
Stored Procedures & Triggers
Stored procedures are precompiled SQL statements that can be executed repeatedly. Triggers are special procedures that automatically execute in response to certain events on a table.
Example: Stored Procedure
CREATE PROCEDURE AddEmployee
@Name VARCHAR(100),
@Department VARCHAR(50),
@Salary DECIMAL(10,2)
AS
BEGIN
INSERT INTO Employees (Name, Department, Salary)
VALUES (@Name, @Department, @Salary)
END;
Example: Trigger
CREATE TRIGGER trg_AuditSalaryChange
ON Employees
FOR UPDATE
AS
BEGIN
IF UPDATE(Salary)
BEGIN
INSERT INTO SalaryAudit (EmployeeID, OldSalary, NewSalary, ChangeDate)
SELECT d.EmployeeID, d.Salary, i.Salary, GETDATE()
FROM deleted d
INNER JOIN inserted i ON d.EmployeeID = i.EmployeeID
END
END;
Sample Tables
| EmployeeID | Name | Department | Salary |
|---|---|---|---|
| 1 | John Doe | HR | 50000 |
| 2 | Jane Smith | IT | 70000 |
| EmployeeID | OldSalary | NewSalary | ChangeDate |
|---|---|---|---|
| 2 | 70000 | 75000 | 2023-10-01 |
SQL Transactions & ACID Properties
Transactions ensure that a group of SQL operations are executed as a single unit. ACID properties (Atomicity, Consistency, Isolation, Durability) guarantee reliable processing of database transactions.
Example: Transaction
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance – 1000 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 2;
IF @@ERROR <> 0
ROLLBACK TRANSACTION;
ELSE
COMMIT TRANSACTION;
Sample Table
| AccountID | CustomerName | Balance |
|---|---|---|
| 1 | Alice | 5000 |
| 2 | Bob | 3000 |
SQL Error Handling
Example: TRY…CATCH
BEGIN TRY
DECLARE @Result INT;
SET @Result = 10 / 0;
END TRY
BEGIN CATCH
PRINT ‘Error occurred: ‘ + ERROR_MESSAGE();
END CATCH;
Practice Exercises
- Create a stored procedure to update employee salary.
- Write a trigger to log changes in employee department.
- Implement a transaction to transfer funds between accounts.
- Use TRY…CATCH to handle divide-by-zero errors.
Real-World Project: Payroll Management System
Design a payroll system using stored procedures for salary calculation, triggers for audit logging, and transactions for bonus distribution.
Schema Overview
| EmployeeID | Name | BaseSalary | Bonus |
|---|---|---|---|
| 1 | John Doe | 50000 | 5000 |
| 2 | Jane Smith | 70000 | 7000 |
FAQs
What is the difference between a stored procedure and a trigger?
A stored procedure is executed explicitly by a user or application, while a trigger is executed automatically in response to a specific event on a table.
What are ACID properties?
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable transaction processing in databases.
How does SQL handle errors?
SQL uses TRY…CATCH blocks to catch and handle errors during query execution, allowing for custom error messages and rollback operations.