Difference Between Trigger and Stored Procedure in SQL Server

In this article, we learn about the difference between triggers and stored procedures.

TRIGGER

• A Trigger is nothing but logic. A Trigger is a stored procedure it runs automatically when an event occurs.
• Syntax:

CREATE TRIGGER trigger_name

ON table_name

AFTER {[INSERT],[UPDATE],[DELETE]}

AS

BEGIN

{sql_statement}

END

STORED PROCEDURE

• 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.
• Syntax:

CREATE PROCEDURE proc_name

AS

Begin

{sql_statement}

End

DIFFERENCE BETWEEN TRIGGER AND STORED PROCEDURE

TRIGGER:-

• 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.

STORED PROCEDURE:-

• 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: -