What is the difference between a trigger and a stored procedure?

Vaibhav Kothia May 30, 2023 0 Comments

A 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

What is the purpose of triggers in SQL Server?

Vaibhav Kothia May 26, 2023 0 Comments

Triggers in SQL Server are special stored procedures that are automatically executed in response to an event such as an INSERT, DELETE, or UPDATE statement on a given table. They are used to enforce business rules, maintain data integrity, and to audit changes to data.

For example, a trigger could be used to prevent a user from deleting a record from a table if it is referenced in another table. The trigger would check if the record is referenced in another table and if so, it would raise an error and not allow the delete to occur.

What is the purpose of a trigger in Oracle Database?

Vaibhav Kothia May 26, 2023 0 Comments

A trigger is a stored PL/SQL block that is executed automatically when a particular event occurs. Triggers are used to enforce business rules and to maintain data integrity.

For example, you can create a trigger that is fired when an INSERT statement is issued on a particular table. The trigger can check to make sure that the data being inserted meets certain criteria before it is accepted into the table.