What is the Difference Between Trigger and Procedure

The main difference between Trigger and Procedure is that a trigger is used to perform an action automatically when an event occurs while a procedure is used to perform a specific task when it is called.

Oracle is a Relational Database Management System developed by Oracle Corporation. The programmer can perform various operations on its databases. Trigger and procedure are two such operations. PL/SQL language is the language to write queries on these databases.

Key Areas Covered

1. What is Trigger
     – Definition, Functionality
2. What is Procedure
     – Definition, Functionality
3. What is the Difference Between Trigger and Procedure
     – Comparison of Key Differences

Key Terms

Oracle, Procedure, Trigger

What is Trigger

Oracle engine invokes triggers when certain events occur. A trigger will be invoked when a specific condition is met. There can be triggers on tables, views, schemas or databases, etc. according to the scenario.

We can write a trigger to respond any of the below events.

When executing statements with INSERT, UPDATE, DELETE etc. In other words, when executing Data Manipulation Language statements.

When executing statements with CREATE, DROP, ALTER etc. In other words, when executing Data Definition Language Statements.

When performing database operations such as LOGON, LOGOFF, STARTUP, SHUTDOWN, etc.

Overall, a trigger provides several advantages. It secures the data and prevents invalid transactions. It also synchronizes the multiple tables. Usually, these databases maintain the consistent relationship among the tables. We call this property referential integrity. Triggers help to enforce referential integrity. Moreover, we can use triggers in event logging and auditing.

What is Procedure

A procedure can perform a specific task. It is similar to a function in other programming languages. A procedure consists of two main sections: header and body. The header section consists of the name of the procedure. It also contains the variables or parameters passed to it.

The body consists of a declaration section, execution section, and an exception section. Declaration section includes declarations of constants, variables, etc. The execution section contains the statements to perform the task. Finally, the exception section has the code that is capable of handling run time errors.

There can be three types of parameters in a procedure as follows. 

IN parameters – Procedure can refer the IN parameter, but the procedure cannot overwrite the value of the parameter.

OUT parameter – Procedure cannot refer the OUT parameter, but the procedure can overwrite the value of the parameter.

INOUT parameter – Procedure can refer the INOUT parameter, and it can overwrite the value of that parameter.

Difference Between Trigger and Procedure

Definition

Triggers are stored programs which are automatically executed or fired when some event occurs while the procedure is a group of statements that can perform a particular task on the database when calling it. Thus, this is the main difference between Trigger and Procedure.

Usage

Another major difference between Trigger and Procedure is their usage. Trigger helps to perform an action automatically when some kind of event occurs while procedure helps to perform a specific task when it is called.

Conclusion

Trigger and procedure are two operations that we can perform on a relational database such as Oracle. The difference between Trigger and Procedure is that trigger is used to perform an action automatically when an event occurs while the procedure is used to perform a specific task when it is called.

Reference:

1. “PL/SQL Trigger – Javatpoint.” Www.javatpoint.com, Available here.
2. “PL/SQL Procedure – Javatpoint.” Www.javatpoint.com, Available here.
3. “What Is Referential Integrity (RI)? – Definition from Techopedia.” Techopedia.com, Available here.

Image Courtesy:

1. “Fixing the database” by Håkan Dahlström (CC BY 2.0) via Flickr