Open In App

Difference between Trigger and Procedure in DBMS

Improve
Improve
Like Article
Like
Save
Share
Report

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. Parameters Triggers Procedures
1. Basics A 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. Action When 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/ call Only 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. Syntax In a database, the syntax to define a trigger: CREATE TRIGGER TRIGGER_NAME In a database, the syntax to define a procedure: CREATE PROCEDURE PROCEDURE_NAME
5. Transaction statements Transaction 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. Usage Triggers 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 value We 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
Previous
Next
Share your thoughts in the comments
Similar Reads