Difference Between Trigger and Procedure

In this post, we will understand the difference between trigger and a procedure.

Triggers

• It is implicitly invoked when an event such as INSERT, DELETE, and UPDATE occurs in a table of a database.

• Nesting of triggers can be achieved using a table.

• A trigger can’t be called or defined inside another trigger.

• Transactional statements such as ‘COMMIT’, ‘ROLLBACK’, ‘SAVEPOINT’ can’t be used in triggers.

• They are used to maintain referential integrity.

• This is done by keeping a record of the activities performed on a table.

• No values are returned in a trigger.

• No value can be passed as a parameter to a trigger.

Syntax to define a trigger:

CREATE TRIGGER trigger_name

Procedures

• It is called explicitly by a user or an application using statements like ‘exec’, ‘EXECUTE’, or a ‘procedure_name’.

• It can be called as well as defined inside another procedure.

• Transactional statements such as ‘COMMIT’, ‘ROLLBACK’, ‘SAVEPOINT’ can be used in procedures.

• Procedures are used to perform tasks that are specified by users.

• Values can be passed as parameters to procedure.

• It can return any value between 0 and ‘n’.

Syntax to define a procedure:

CREATE PROCEDURE procedure_name