This section describes the different types of triggers:
- Row Triggers and Statement Triggers
- BEFORE and AFTER Triggers
- INSTEAD OF Triggers
- Triggers on System Events and User Events
Row Triggers
A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.
BEFORE and AFTER Triggers
When defining a trigger, you can specify the trigger timing--whether the trigger action is to be run before or after the triggering statement. BEFORE and AFTER apply to both statement and row triggers.
Difference between Trigger and Procedure
Triggers | Stored Procedures |
In trigger no need to execute manually. Triggers will be fired automatically. Triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table. | Where as in procedure we need to execute manually. |
Differences between stored procedure and functions
Stored Procedure | Functions |
Stored procedure may or may not return values. | Function should return at least one output parameter. Can return more than one parameter using OUT argument. |
Stored procedure can be used to solve the business logic. | Function can be used to calculations |
Stored procedure is a pre-compiled statement. | But function is not a pre-compiled statement. |
Stored procedure accepts more than one argument. | Whereas function does not accept arguments. |
Stored procedures are mainly used to process the tasks. | Functions are mainly used to compute values |
Cannot be invoked from SQL statements. E.g. SELECT | Can be invoked form SQL statements e.g. SELECT |
Can affect the state of database using commit. | Cannot affect the state of database. |
Stored as a pseudo-code in database i.e. compiled form. | Parsed and compiled at runtime. |
No comments:
Post a Comment