What is a Trigger

What is a trigger?





A database trigger is procedural (technical) code that is automatically executed in response to

certain events on a particular table in a database. Triggers can restrict access to specific data,

perform logging, or audit access to data.



There are two classes of triggers, they are either "row triggers" or "statement triggers". With row

triggers you can define an action for every row of a table, while statement triggers only occur

once and are not dependent on the shape of the data.



Each class can be of several types. There are "BEFORE triggers" and "AFTER triggers" which

alters the time of execution of the trigger. There is also an "INSTEAD OF trigger" which is a

conditional trigger that will fire instead of the triggering statement.



There are typically three triggering EVENTS that cause trigger to 'fire':



 INSERT event (as a new record is being inserted into the database).

 UPDATE event (as a record is being changed).

 DELETE event (as a record is being deleted).



Databases that support triggers typically give programmers access to record variables by means

of a syntax such as :OLD.cust_name or :NEW.cust_name. e.g. if a trigger is monitoring for

changes to a salary field one could write a trigger.



BEFORE UPDATE ON employee_table

FOR ALL records

IF :NEW.salary :OLD.salary THEN

do something here

END IF;

END;



Oracle 9i allows you to create triggers on schemas and databases also.You can create a trigger

for creating or altering or dropping a schema object.Triggers can also be created for logging in

and logging out of databases.



Schema Level Triggers



 Before Create

 After Create

 Before Alter

 After Alter

 Before Drop

 After Drop

 After Logon

 Before Logoff



The major features and effects of database triggers are that they:



 do not accept parameters or arguments

 can commit/rollback only through autonomous transactions

 can cause mutating table errors, if they are poorly written.

What is a trigger?

In a DBMS, a trigger is a SQL procedure that initiates an action (i.e., fires an action) when an event

(INSERT, DELETE or UPDATE) occurs. Since triggers are event-driven specialized procedures, they are

stored in and managed by the DBMS. A trigger cannot be called or executed; the DBMS automatically fires

the trigger as a result of a data modification to the associated table. Triggers are used to maintain the

referential integrity of data by changing the data in a systematic fashion.



Each trigger is attached to a single, specified table in the database.



Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored

at the database level. Stored procedures, however, are not event-drive and are not attached to a specific

table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while

triggers are implicitly executed. In addition, triggers can also execute stored procedures.



A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired

because of data modification it can also cause another data modification, thereby firing another trigger. A

trigger that contains data modification logic within itself is called a nested trigger.



What is a trigger?

Rating:

Trigger

A trigger is a kind of procedure that executes implicitly when a table or view is modified or some

user actions or database system actions occur. Similar to a stored procedure, a trigger stored in

the database can include SQL and PL/SQL or Java statements to run as a unit and can invoke

stored procedures. However, procedures and triggers differ in the way that they are invoked. A

procedure is explicitly run by a user, application, or trigger. On the other hand, triggers are

implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or

which application is being used.

A trigger can be an application trigger or a database trigger. An application trigger executes

implicitly whenever a particular data manipulation language (DML) event occurs within an

application. An example of an application that uses triggers extensively is one developed with

Oracle Forms Developer.

A database trigger executes implicitly when a triggering event such as a DML statement

(INSERT, UPDATE, or DELETE) on a table or view, or a DDL statement (CREATE or ALTER) is

issued, regardless of which database user is connected or which application is being used. A

database trigger also executes implicitly when some user actions or database system actions

occur, e.g., when a database user logs on to the database or the database administrator (DBA)

shuts down the database.

Parts of a database trigger

A database trigger has the following parts:



Trigger timing: When defining a trigger, the trigger timing can be specified by using the

BEFORE or AFTER option. The trigger timing specifies whether the trigger action is to be

executed before or after the triggering statement.

Triggering event or statement: A triggering event or statement is a SQL statement,

database event, or user event that causes a trigger to fire. A triggering event can be one

or more of the following:



An INSERT, UPDATE, or DELETE statement on a specific table or view

A CREATE, ALTER, or DROP statement on any schema object

A database startup or instance shutdown

A specific error message or any error message

A user logon or logoff

Trigger restriction: A trigger restriction specifies a Boolean expression that must be true

for the trigger to fire. The trigger action is not run if the trigger restriction evaluates to

false or unknown.



Trigger action: A trigger action is the procedure (PL/SQL block, Java program, or C

callout) that contains the SQL statements and code to be run when a triggering statement

is issued and the trigger restriction evaluates to true. A trigger action can contain SQL,

PL/SQL, or Java statements. It can define Java language constructs and PL/SQL

language constructs such as variables, constants, cursors, and exceptions. It can also

call stored procedures. If the trigger is a row trigger, the statements in a trigger action

have access to column values of the row being processed by the trigger. Correlation

names provide access to the old and new values of each column.



Types of triggers





Triggers can be classified on the basis of the following trigger components:



Trigger timing

Triggering event or statement



Based on the trigger timing, triggers are of the following two types:



BEFORE trigger: A BEFORE trigger executes the trigger action before the triggering

statement is executed. It is generally used when the trigger action determines whether

the triggering statement should be allowed to complete. By using a BEFORE trigger for

this purpose, the unnecessary processing of the triggering statement and its eventual

rollback (in case an exception is raised in the trigger action) can be eliminated.





BEFORE triggers are also used to derive specific column values before completing a

triggering INSERT or UPDATE statement, to initialize global variables or flags, and to

validate complex business rules.



AFTER trigger: An AFTER trigger executes the trigger action after the triggering

statement is executed. An AFTER trigger is generally used to perform different actions on

the same triggering statement if a BEFORE trigger is already present.





When using triggers to provide sophisticated auditing, AFTER triggers are given priority.

By using AFTER triggers, auditing information is recorded after the triggering statement is

subjected to applicable integrity constraints, thereby preventing cases where the audit

processing is carried out unnecessarily for statements that generate exceptions to

integrity constraints.



Note: BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not

on views. However, triggers on the base tables of a view are fired if an INSERT, UPDATE, or

DELETE statement is issued against the view. BEFORE and AFTER triggers fired by DDL

statements can be defined only on the database or a schema, not on particular tables.





Based on the triggering event or statement, triggers can be of the following types:



DML trigger: A DML trigger is a database trigger whose triggering event is a Data

Manipulation Language (DML) statement such as an INSERT, UPDATE, or DELETE

statement. A DML trigger can be a statement trigger or a row trigger.



1. Statement trigger: A statement trigger defined for a table is fired once on behalf

of the triggering statement, regardless of the number of affected rows, even if no

rows are affected at all. For example, if a DELETE statement deletes several

rows from a table, a statement-level DELETE trigger is fired only once.





Statement triggers are useful if the code in the trigger action does not depend on

the data provided by the triggering statement or affected rows. A statement

trigger is generally used to make a complex security check on the current time or

user, to generate a single audit record, etc.



2. Row trigger: A row trigger defined for a table 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 the triggering statement does not affect any row,

the row trigger is not fired.





Row triggers are useful if the code in the trigger action depends on the data

provided by the triggering statement or affected rows.





BEFORE and AFTER options apply to both statement and row triggers. Hence,

by using the BEFORE and AFTER options, the following four types of row and

statement triggers can be created:

1. BEFORE statement trigger: This trigger runs the trigger action before the

triggering statement is run.

2. BEFORE row trigger: This trigger runs the trigger action before modifying

each row affected by the triggering statement and before checking

appropriate integrity constraints, provided the trigger restriction was not

violated.

3. AFTER statement trigger: This trigger runs the trigger action after the

triggering statement is run and integrity constraints, if any, are applied.

4. AFTER row trigger: This trigger runs the trigger action after modifying

each row affected by the triggering statement and applying integrity

constraints, provided the trigger restriction was not violated. Unlike

BEFORE ROW triggers, AFTER ROW triggers lock rows.







A DML trigger can also be defined on a view by using the INSTEAD OF option. A DML

trigger using the INSTEAD OF option is known as an INSTEAD OF trigger.



o INSTEAD OF trigger: An INSTEAD OF trigger provides a transparent way of

modifying a view that cannot be modified directly through DML (Data

Manipulation Language) statements such as INSERT, UPDATE, and DELETE.

An INSTEAD OF trigger is so called because, unlike other types of triggers,

Oracle fires the trigger instead of executing the triggering statement.





An INSTEAD OF trigger defined on a view is fired when an INSERT, UPDATE, or

DELETE statement is executed against the view. When fired, the INSTEAD OF

trigger modifies the underlying tables appropriately. An INSTEAD OF trigger

defined on a view is always a row trigger, i.e., the trigger is fired for each

modified row of the view.





INSTEAD OF triggers can be designed only for views and not for tables. The

BEFORE and AFTER options cannot be used with INSTEAD OF triggers.









DDL trigger: A DDL trigger is a database trigger whose triggering event is a Data

Definition Language (DDL) statement, i.e., a DDL trigger fires when a DDL statement

(such as a CREATE, ALTER, or DROP statement) is executed in the database or a

particular schema.



DDL Function

Event

CREATE Causes the Oracle server to fire the trigger whenever a CREATE statement creates a

new database object in the database or schema.

ALTER Causes the Oracle server to fire the trigger whenever an ALTER statement modifies a

database object in the database or schema.

DROP Causes the Oracle server to fire the trigger whenever a DROP statement removes a

database object from the database or schema.







A DDL trigger can be created at either the database level or schema level. The BEFORE

or AFTER option can also be specified for defining the timing of the trigger.





A DDL trigger fires only if the database object being created is a cluster, function, index,

package, procedure, role, sequence, synonym, table, tablespace, trigger, type, view, or

user



System event trigger: A system event trigger is a database trigger defined on a system

event. A system event trigger fires when a system event (such as startup, shutdown,

logon, logoff, or error) occurs. Database triggers on system events can be defined at the

database or schema level. For example, a trigger on database startup or shutdown can

be defined only at the database level. However, a trigger on user logon or logoff can be

defined at either the database level or schema level.





Database triggers can be created on the following system events:



System Event Description

SERVERERROR Causes the Oracle server to fire the trigger whenever a server error message is

logged.

LOGON Causes the Oracle server to fire the trigger whenever a user logs on to the

database.

LOGOFF Causes the Oracle server to fire the trigger whenever a user logs off the

database.

STARTUP Causes the Oracle server to fire the trigger whenever the database is opened.

SHUTDOWN Causes the Oracle server to fire the trigger whenever the database is shut

down.







Note:

The trigger on a SERVERERROR event can only be an AFTER trigger.

The trigger on a LOGON event can only be an AFTER trigger.

The trigger on a LOGOFF event can only be a BEFORE trigger.

The trigger on a STARTUP event can only be an AFTER trigger.

The trigger on a SHUTDOWN event can only be a BEFORE trigger.



A database trigger can be a system trigger on a database or schema. A database trigger defined

on a database fires for each event for all users. However, a database trigger defined on a

schema fires for each event for that particular user.

SQL Server Stored Procedures

Definition: Microsoft SQL Server is a powerful relational database management system catering to high-

end users with advanced needs. Along with Oracle, Microsoft SQL Server is widely regarded as one of the

two main full-featured database systems on the market today.



Definition: Stored procedures are precompiled database queries that improve the security, efficiency and

usability of database client/server applications. Developers specify a stored procedure in terms of input and

output variables. They then compile the code on the database platform and make it available to application

developers for use in other environments, such as web applications. All of the major database platforms,

including Oracle, SQL Server and MySQL support stored procedures. The major benefits of this technology

are the substantial (Large- Considerable) performance gains from precompiled execution, the reduction of

client/server traffic, development efficiency gains from code reuse and abstraction and the security controls

inherent in granting users permissions on specific stored procedures instead of the underlying database

tables.



Definition: Transact-SQL is Microsoft's proprietary extension to the Structured Query Language (SQL). It

includes additional functionality designed to support Microsoft SQL Server.



Benefits of Stored Procedures

Why should you use stored procedures? Let's take a look at the key benefits of this technology:



 Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the

execution plan. This results in tremendous performance boosts when stored procedures are called

repeatedly.

 Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be

happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over

the wire.

 Efficient reuse of code and programming abstraction.

 Stored procedures can be used by multiple users and client programs. If you utilize them in a

planned manner, you'll find the development cycle takes less time.

 Enhanced security controls. You can grant users permission to execute a stored procedure

independently of underlying table permissions.



Structure

Stored procedures are extremely similar to the constructs seen in other programming languages. They

accept data in the form of input parameters that are specified at execution time. These input parameters (if

implemented) are utilized in the execution of a series of statements that produce some result. This result is

returned to the calling environment through the use of a recordset, output parameters and a return code.

That may sound like a mouthful, but you'll find that stored procedures are actually quite simple.



Example

Let's take a look at a practical example. Assume we have the table shown at the bottom of this page, named

Inventory. This information is updated in real-time and warehouse managers are constantly checking the

levels of products stored at their warehouse and available for shipment. In the past, each manager would

run queries similar to the following:

SELECT Product, Quantity

FROM Inventory

WHERE Warehouse = 'FL'

This resulted in very inefficient performance at the SQL Server. Each time a warehouse manager executed

the query, the database server was forced to recompile the query and execute it from scratch. It also

required the warehouse manager to have knowledge of SQL and appropriate permissions to access the

table information.



We can simplify this process through the use of a stored procedure. Let's create a procedure called

sp_GetInventory that retrieves the inventory levels for a given warehouse. Here's the SQL code:

CREATE PROCEDURE sp_GetInventory

@location varchar(10)

AS

SELECT Product, Quantity

FROM Inventory

WHERE Warehouse = @location

Our Florida warehouse manager can then access inventory levels by issuing the command

EXECUTE sp_GetInventory 'FL'

The New York warehouse manager can use the same stored procedure to access that area's inventory.

EXECUTE sp_GetInventory 'NY'

Granted, this is a simple example, but the benefits of abstraction can be seen here. The warehouse

manager does not need to understand SQL or the inner workings of the procedure. From a performance

perspective, the stored procedure will work wonders. The SQL Sever creates an execution plan once and

then reutilizes it by plugging in the appropriate parameters at execution time.



Now that you've learned the benefits of stored procedures, get out there and use them! Try a few examples

and measure the performance enhancements achieved -- you'll be amazed!



Inventory Table



ID Product Warehouse Quantity

142 Green beans NY 100

214 Peas FL 200

825 Corn NY 140

512 Lima beans NY 180

491 Tomatoes FL 80

379 Watermelon FL 85