Sunday, August 1, 2010

PL/ SQL triggers

 
===========

Prerequisites




Before a trigger can be created, the user SYS must run a SQL script commonly called DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.



To create a trigger in your own schema on a table in your own schema or on your own schema (SCHEMA), you must have the CREATE TRIGGER system privilege.



To create a trigger in any schema on a table in any schema, or on another user's schema (schema.SCHEMA), you must have the CREATE ANY TRIGGER system privilege.



In addition to the preceding privileges, to create a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege.
 
===================
 
Before triggers

-- Used when values are inserted or updated from a derived value.
-- also used when trigger must decide wtheter to allow or not to allow DML.



BEFORE
Specify BEFORE to cause the database to fire the trigger before executing the triggering event. For row triggers, the trigger is fired before each affected row is changed.

Restrictions on BEFORE Triggers
BEFORE triggers are subject to the following restrictions:

You cannot specify a BEFORE trigger on a view or an object view.
You can write to the :NEW value but not to the :OLD value.

-- Boolean functions:- updating, inserting, deleting

===========================================
After trigger:-

You should use AFTER triggers in the following situations:


. When a trigger should fire after a DML statement is executed

. When a trigger performs actions not specified in a BEFORE trigger.

AFTER




Specify AFTER to cause the database to fire the trigger after executing the triggering event. For row triggers, the trigger is fired after each affected row is changed.



Restrictions on AFTER Triggers

AFTER triggers are subject to the following restrictions:



You cannot specify an AFTER trigger on a view or an object view.



You cannot write either the :OLD or the :NEW value.



Note:

When you create a materialized view log for a table, Oracle Database implicitly creates an AFTER ROW trigger on the table. This trigger inserts a row into the materialized view log whenever an INSERT, UPDATE, or DELETE statement modifies data in the master table. You cannot control the order in which multiple row triggers fire. Therefore, you should not write triggers intended to affect the content of the materialized view.
 
============================
 
statement level triggers and row level triggers
 
 
Statement triggers should be used when the operations performed by the trigger do not depend


on the data in the individual records.

----------------------------

instead of triggers

---------------------

Compound triggers :- allows different triggers comined into one.

No comments:

Post a Comment