Difference Between Trigger and Stored Procedure in SQL Server
In this article, we learn about the difference between triggers and stored procedures.
- A Trigger is nothing but logic. A Trigger is a stored procedure it runs automatically when an event occurs.
CREATE TRIGGER trigger_name
- A stored procedure is a set of SQL statements. We can pass the value as a parameter and perform some tasks.
- The stored procedure helps to code re-usability.
CREATE PROCEDURE proc_name
DIFFERENCE BETWEEN TRIGGER AND STORED PROCEDURE
- Trigger executes implicitly. Whenever an event INSERT, UPDATE, and DELETE occurs it executed automatically.
- We cannot define a trigger inside another trigger.
- Transaction statements are not allowed in the trigger.
- We cannot return value in a trigger.
- A Procedure executed explicitly when the user using statements such as exec, EXECUTE, etc.
- We can define procedures inside another procedure. Also, we can use functions inside the stored procedure.
- Transaction statements such as COMMIT, ROLLBACK, and SAVEPOINT are allowed in the procedure.
- Stored procedures return a zero or N value. However, we can pass values as parameters.
- Return keyword used to exit the procedure.
If you are a newbie to database learning — SQL Server recommended is the following must-watch video: -