Triggers and Stored Procedures
Triggers
What is a Trigger?
A Trigger is a special kind of stored procedure that automatically runs (fires) when a specific event occurs in a database table—such as an INSERT, UPDATE, or DELETE operation.
Key Characteristics:
- Triggers are event-driven.
- They cannot be called manually—they execute automatically.
- Useful for enforcing business rules, audit trails, or automatic updates.
This trigger ensures that whenever a new employee is inserted, the JoinDate is automatically set to the current date.
Types of Triggers:
- BEFORE Trigger: Executes before the triggering action.
- AFTER Trigger: Executes after the triggering action.
- INSTEAD OF Trigger: Used mostly in views to replace the action.
Stored Procedures
What is a Stored Procedure?
A Stored Procedure is a named block of SQL code that performs a task and is stored in the database. It can be called manually by the user or by an application.
Key Characteristics:
- They encapsulate logic that can be reused.
- Accept parameters and can return results.
- Improve performance and security by limiting direct SQL execution.
Example Use Case:
Create a procedure to increase the salary of an employee by a given percentage.
This increases the salary of employee with ID 101 by 10%.
Key Differences: Trigger vs Stored Procedure
| Feature | Trigger | Stored Procedure |
|---|---|---|
| When it runs | Automatically on table events | Manually called by user/app |
| Use case | Auditing, validation, automation | Business logic, complex operations |
| Input Parameters | No | Yes |
| Direct execution | No | Yes |
