Introduction to Triggers

Triggers are like to the procedures stored in Java, PL/SQL or call out to a C procedure but these are implicitly implemented by database due to some kinds of database actions:

• a table or view is modified with DML DDL Statements
• some user actions issued either by a particular schema/user or by any schema/user
• database system actions such as logon/logoff, errors, or startup/shutdown

What is difference between stored procedures and triggers?

Procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

Oracle provides a highly customized database management system with triggers )

You can use triggers for all these operations:

• restrict DML operations against a table to those issued during regular business hours
• Automatically generate derived column values
• Prevent invalid transactions
• Enforce complex security authorizations
• Enforce referential integrity across nodes in a distributed database
• Enforce complex business rules
• Provide transparent event logging
• Provide auditing
• Maintain synchronous table replicates
• Gather statistics on table access
• Modify table data when DML statements are issued against views
• Publish information about database events, user events, and SQL statements to subscribing applications

Be careful how to use triggers!….

all of them can be problems in complex interdependencies and it is generally hard to handle.

Use always integrity constraints as usable as possible!….

• Oracle strongly recommends that you use triggers to constrain data input only in the following situations:
• To enforce referential integrity when child and parent tables are on different nodes of a distributed database
• To enforce complex business rules not definable using integrity constraints
• When a required referential integrity rule cannot be enforced using the following integrity constraints:

NOT NULL, UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK,DELETE CASCADE,DELETE SET NULL

Structure of Triggers

Components of Triggers

• A triggering event or statement

A triggering events might be one or group of database events. As you remember:

An INSERT, UPDATE, or DELETE statement on a specific table (or view, in some cases)

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

• A trigger restriction

This is like check constraints on tables to check the boolean results from the defined restriction to fire a trigger or not. Such as

new.parts_on_hand < new.reorder_point

• A trigger action

After definition of the event and restriction if necessary you can write your own procedure to implement implicitly in database. As we define, these are action types:

Contain SQL, PL/SQL, or Java statements

Define PL/SQL language constructs such as variables, constants, cursors, exceptions

Define Java language constructs. Call stored procedures

Syntax of Triggers

CREATE [OR REPLACE] TRIGGER <trigger_name>

{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>

[REFERENCING [NEW AS <new_row_name>] [OLD AS <old_row_name>]]

[FOR EACH ROW [WHEN (<trigger_condition>)]]

<trigger_body>

~ by enginzorlu on August 16, 2007.