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.