Open In App
Related Articles

Difference between Trigger and Procedure in DBMS

Improve Article
Save Article
Like Article

1. Procedures: A procedure is a combination of SQL statements written to perform specified tasks. It helps in code re-usability and saves time and lines of code. 

Advantages of Procedures:

  • A Stored Procedure can be used as modular programming, which means that it can be created once, stored, and called multiple times as needed. This allows for speedier execution.
  • Reduces network traffic 
  • Improving data security
  • Easy to maintain because the stored procedure scripts are all in one place and hence, it is easy to update and track dependencies when schema changes occur.
  • Testing can be carried out independent of the application.

2. Triggers: A trigger is a special kind of procedure that executes only when some triggering event such as INSERT, UPDATE, or DELETE operations occur in a table. 

Advantages of Triggers:

  • Protection of data
  • Inhibits transactions that are not valid
  • It also keeps the different tables in sync.
  • Referential integrity is enforced with the use of triggers. 
  • Triggers can also be used in event logging and auditing.

Difference between Triggers and Procedures:

S. No.ParametersTriggersProcedures
1.BasicsA Trigger is implicitly invoked whenever any event such as INSERT, DELETE, or UPDATE occurs in a TABLE.A Procedure is explicitly called by the user/application using statements or commands such as exec, EXECUTE, or simply procedure name
2.ActionWhen an event occurs, a trigger helps to execute an action automatically.A procedure helps to perform a specified task when it is invoked.
3.Define/ callOnly nesting of triggers can be achieved in a table. We cannot define/call a trigger inside another trigger.We can define/call procedures inside another procedure.
4.SyntaxIn a database, the syntax to define a trigger: CREATE TRIGGER TRIGGER_NAMEIn a database, the syntax to define a procedure: CREATE PROCEDURE PROCEDURE_NAME
5.Transaction statementsTransaction statements such as COMMIT, ROLLBACK, and SAVEPOINT are not allowed in triggers.All transaction statements such as COMMIT and ROLLBACK are allowed in procedures.
6.UsageTriggers are used to maintain referential integrity by keeping a record of activities performed on the table.Procedures are used to perform tasks defined or specified by the users.
7.Return valueWe cannot return values in a trigger. Also, as an input, we cannot pass values as a parameter.We can return 0 to n values. However, we can pass values as parameters.
Last Updated : 16 Jun, 2022
Like Article
Save Article
Similar Reads
Related Tutorials