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.
Difference between Triggers and 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.|
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.