Tuesday, January 12, 2021

Difference between Trigger and Procedure in DBMS

Oracle Database Tutorial and Material, Oracle Database Exam Prep, Oracle Database Prep, Database Certification, Database Career

1. Procedures :

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

2. Triggers :

A trigger is a special kind of procedure which executes only when some triggering event such as INSERT, UPDATE, DELETE operations occurs in a table.

Oracle Database Tutorial and Material, Oracle Database Exam Prep, Oracle Database Prep, Database Certification, Database Career
Difference between Triggers and Procedures :

Triggers Procedures 
A Trigger is implicitly invoked whenever any event such as INSERT, DELETE, UPDATE occurs in a TABLE.  A Procedure is explicitly called by user/application using statements or commands such as exec, EXECUTE, or simply procedure_name
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. 
In a database, syntax to define a trigger: CREATE TRIGGER TRIGGER_NAME  In a database, syntax to define a procedure: CREATE PROCEDURE PROCEDURE_NAME 
Transaction statements such as COMMIT, ROLLBACK, SAVEPOINT are not allowed in triggers.  All transaction statements such as COMMIT, ROLLBACK are allowed in procedures. 
Triggers are used to maintain referencial integrity by keeping a record of activities performed on the table.  Procedures are used to perform tasks defined or specified by the users. 
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. 

Related Posts

0 comments:

Post a Comment