Sunday, July 29, 2012

What are theTypes of Triggers ?


This section describes the different types of triggers:

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.
BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not on views.
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: