java2db

PL/SQL Triggers



PL/SQL Triggers are the programs that  execute in response to changes in table data or certain database events. A predefined set of events can be “hooked” with a trigger, enabling you to integrate your own processing with that of the database.A triggering event fires or executes the trigger.

 

PL/SQL TriggersThere are three types of triggering events:

PL/SQL Triggers DML          :  Fires when executed the DML ( like INSERT,UPDATE or DELETE) statements.
PL/SQL Triggers DDL          :  Fires when executed the DDL  (like  CREATE ,ALTER or DROP) statements .
PL/SQL Triggers Database :   Fires when one of the predefined database-level events occur.

Trigger events are listed in the following table:

Trigger event Description
INSERT Fires whenever a row is added to the table_or_view_reference.
UPDATE Fires whenever an UPDATE changes the table_or_view_reference.UPDATE triggers can additionally specify an OF clause to restrict firing to updates OF certain columns.
ALTER Fires whenever an ALTER statement changes a database object.In this context,objects are things such as tables or packages.Can apply to single schema or the entire database.
ANALYZE Fires whenever the database collects or deletes statistics or validates the structure of a database object.
ASSOCIATE STATISTICS Fires whenever the database associates a statistic type with a database object.
AUDIT Fires whenever the database records an audit operation.
COMMENT Fires whenever a comment on a database object is modified.
CREATE Fires whenever a database object is created.Does not fire on CREATE CONTROLFILE statements.
DB_ROLE_CHANGE In a Data Guard configuration, fires whenever a role change from primary to standby or standby to primary occurs.Only AFTER DB_ROLE_CHANGE triggers on the DATABASE are allowed.
DELETE Fires whenever a row is deleted form the table_or_view_reference.Does not fire on TRUNCATE of the table.
DISASSOCIATE STATISTICS  Fires whenever the database disassociates a statistic type from a database object.
DROP Fires whenever a DROP statement removes an object from the database. In this context,objects are things such as tables or packages . Can apply to a single schema or the entire database.
GRANT Fires whenever a system , role , or object privilege is assigned.
NOAUDIT Fires whenever the database processes a NOAUDIT statement to stop auditing database operations.
RENAME Fires whenever a RENAME statement changes a database object name.
REVOKE Fires whenever a system , role , or object privilege is rescinded.
TRUNCATE Fires whenever a TRUNCATE statement is processed to purge a table or cluster.
SERVERERROR Fires whenever a server error message is logged. Only AFTER triggers are allowed in this context.
LOGON Fires whenever a session is created( a user connects to the database). Only AFTER triggers are allowed in this context.
LOGOFF Fires whenever a session is terminated (a user disconnects from the database). Only BEFORE triggers are allowed in this context.
STARTUP Fires when the database is opened.Only AFTER triggers are allowed in this context.
SHUTDOWN Fires when the database is closed.Only BEFORE triggers are allowed in this context.
SUSPEND Fires whenever a server error causes a transaction to be suspended.

 

 

PL/SQL Triggers can fire BEFORE or AFTER the triggering event.AFTER DML triggers are slightly more efficient than BEFORE triggers.
FOR EACH ROW defines the trigger to be a row-level trigger. Row-Level triggers fire once for each row affected.

The WHEN trigger_condition specifies the conditions that must be met for the trigger to fire.Store functions and object methods are not allowed in the trigger condition.

 

PL/SQL Triggers  Compound DML Triggers:

Oracle Database 11g , allows you to combine up to four DML triggering events into a single coordinated program.A compound trigger has as many as four sections.

PL/SQL Triggers  A BEFORE STATEMENT section :

Before the triggering statement runs.

 

PL/SQL Triggers  AFTER STATEMENT section:

After the triggering statement runs.

 

PL/SQL Triggers  A BEFORE EACH ROW section :

Before each row that the triggering statement affects.Use BEFORE EACH ROW when you need to WRITE to the :new record.

 

PL/SQL Triggers  AFTER EACH ROW section:

After each row that the triggering statement affects.Use AFTER EACH row triggers when you want to VALIDATE the final values in the :new record

The FOR and COMPOUND TRIGGER keywords tell the database that the trigger is a compound trigger.

PL/SQL Triggers Difference between Statement level and Row level  PL/SQL Triggers:

 Statement level trigger :

PL/SQL Triggers  Trigger will be fired only once irrespective of the number of records getting affected
PL/SQL Triggers  The default is a statement-level trigger
PL/SQL Triggers  Cannot use the co-relation identifiers (:old  &  :new).
PL/SQL Triggers  It will fire once ,even though no records affected.
Row level trigger :
PL/SQL Triggers  Trigger will fire to the number of records getting affected
PL/SQL Triggers  FOR EACH ROW will make a statement level trigger to row level trigger.
PL/SQL Triggers  Can use co-relation identifiers.
PL/SQL Triggers  It cannot fire , if no record affects.

 

PL/SQL Triggers Create example tables to test PL/SQL Triggers :

 

After table creation , Create the above four triggers.
If we insert records, the triggers will fire and perform the specified action.In the same in update and delete.

E_NUM
E_NAME
GROSS_SAL
TAX
NET_SAL
1 Rose 17000 2550 14450
2 Marks 16000 2400 13600
3 Alex 18000 2700 15300

In the above table ,Before (row level) trigger will calculate the TAX and NET_SAL  and insert into the emp_sal_details table.

In the emp_sal_history  table, After (row level) trigger will insert the record if we insert,update or delete record from the emp_sal_details.

E_NUM
OLD_GROSS_SAL
NEW_GROSS_SAL
ACT_TYPE
1 0 17000 Insert
2 0 16000 Insert
3 0 18000 Insert



Related Posts :
PL/SQL Arrays and nested tables ( Collections )
IF THEN ELSE and CASE in PL/SQL
PL/SQL FOR and WHILE loop controls
PL/SQL Operators
PL/SQL Procedures
PL/SQL Functions
PL/SQL Variables
   [will not be published]



^ <