Compare the Difference Between Similar Terms

Difference Between

Home / Technology / IT / Database / Difference Between Triggers and Stored Procedures

Difference Between Triggers and Stored Procedures

June 28, 2011 Posted by Indika

Triggers vs Stored Procedures

In a database, a trigger is a procedure (code segment) that is executed automatically when some specific events occur in a table/view. Among its other uses, triggers are mainly used for maintaining integrity in a database. A stored procedure is a method that can be used by applications accessing a relational database. Typically, stored procedures are used as a method for validating data and controlling access to a database.

What are Triggers?

A trigger is a procedure (code segment) that is executed automatically when some specific events occur in a table/view of a database. Among its other uses, triggers are mainly used for maintaining integrity in a database. Triggers are also used for enforcing business rules, auditing changes in the database and replicating data. Most common triggers are Data Manipulation Language (DML) triggers that are triggered when data is manipulated. Some database systems support non-data triggers, which are triggered when Data Definition Language (DDL) events occur. Some examples are triggers that are fired when tables are created, during commit or rollback operations occur, etc. These triggers can be especially used for auditing. Oracle database system supports schema level triggers (i.e. triggers fired when database schemas are modified) such as After Creation, Before Alter, After Alter, Before Drop, After Drop, etc. The four main types of triggers supported by Oracle are Row Level triggers, Column Level triggers, Each Row Type triggers and For Each Statement Type triggers.

What are Stored Procedures?

A stored procedure is a method that can be used by an application accessing a relational database. Typically, stored procedures are used as a method for validating data and controlling access to a database. If some data processing operation requires several SQL statements to be executed, such operations are implemented as stored procedures. When invoking a stored procedure, a CALL or EXECUTE statement has to be used. Stored procedures can return results (for example results from the SELECT statements). These results can be used by other stored procedures or by applications. Languages that are used to write stored procedures typically support control structures such as if, while, for, etc. Depending on the database system used, several languages can be used to implement stored procedures (e.g. PL/SQL and java in Oracle, T-SQL (Transact-SQL) and .NET Framework in Microsoft SQL Server). Furthermore, MySQL uses its own stored procedures.

What is the difference between Triggers and Stored Procedures?

A trigger is a procedure (code segment) that is executed automatically when some specific events occur in a table/view of a database, while a stored procedure is a method that can be used by an application accessing a relational database. Triggers are executed automatically when the event that the trigger is supposed to respond to occurs. But to execute a stored procedure a specific CALL or EXECUTE statement has to be used. Debugging triggers can be harder and trickier than debugging stored procedures. Triggers are very useful when you want to make sure that something happens when a certain event occurs.

Related posts:

Difference Between SQL and PL SQL Difference Between Distributed Database and Centralized Database Difference Between Entity and Attribute Difference Between SAP and ORACLE Difference Between Indexing and Sorting

Filed Under: Database Tagged With: Column Level triggers, DLL triggers, DML triggers, Each Row Type triggers, Each Statement Type triggers, MySQL stored procedures, Row Level triggers, stored procedure, stored procedures, trigger, Triggers, triggers supported by Oracle

About the Author: Indika

Indika, BSc.Eng, MSECE Computer Engineering, PhD. Computer Science, is an Assistant Professor and has research interests in the areas of Bioinformatics, Computational Biology, and Biomedical Natural Language Processing.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Request Article

Featured Posts

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and SARS

Difference Between Coronavirus and SARS

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Covid 19

Difference Between Coronavirus and Covid 19

You May Like

Difference Between Eutrophication and Algal Bloom

Difference Between Eutrophication and Algal Bloom

Difference Between Theme and Moral

Difference Between Mallard and Duck

Difference Between Horsepower and Brake Horsepower

Difference Between LD50 and LC50

Difference Between LD50 and LC50

Latest Posts

  • What is the Difference Between Sharara and Lehenga
  • What is the Difference Between Leucoderma and Albinism
  • What is the Difference Between Cytosolic and Chloroplastic Glycolysis
  • What is the Difference Between Hammer Toe and Mallet Toe
  • What is the Difference Between Osteoporosis and Scoliosis
  • What is the Difference Between Saree and Half Saree
  • Home
  • Vacancies
  • About
  • Request Article
  • Contact Us

Copyright © 2010-2018 Difference Between. All rights reserved. Terms of Use and Privacy Policy: Legal.