What is the difference between a trigger and a stored procedure?
Vaibhav KothiaA trigger is a block of code that is executed automatically when a specific event occurs in a database, such as when a record is inserted, updated, or deleted. Triggers are often used to implement complex business rules, maintain data integrity, or audit changes to data.
Example of a Trigger:
CREATE TRIGGER tr_Employee_Update
ON Employee
AFTER UPDATE
AS
BEGIN
UPDATE Employee
SET LastUpdated = GETDATE()
WHERE EmployeeID =
(SELECT EmployeeID FROM deleted)
END
A stored procedure is a precompiled set of SQL statements that can be executed multiple times with different parameters. Stored procedures are often used to encapsulate complex business logic and are used to improve application performance by reducing the amount of code that needs to be executed.
Example of a Stored Procedure:
CREATE PROCEDURE GetEmployeeInfo
@EmployeeID int
AS
BEGIN
SELECT *
FROM Employee
WHERE EmployeeID = @EmployeeID
END