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