How to customize an oracle10g Database using Database triggers

A trigger is used to define an action that should take place when some database related event occurs. Triggers can be initiated to fire when data manipulation commands or data definition commands or database events occur.

Both triggers and referential integrity constraints can be used to enforce the integrity of the database, however it is recommended to use triggers only if integrity constraints in oracle are unable to meet the requirements of business rules. Triggers can be created on Schema level operations or on Database level operations.

A trigger’s type is defined by the level at which trigger is executed and by the type of transaction. Row level triggers execute once for each row that is affected by a Data Manipulation Statement. They are most commonly used and used especially for data auditing and to keep distributed data in sync. Statement level triggers execute once for each Data Manipulation Statement.

They are normally used for enforcing security measures on the actions that is performed on a table. “Before and After Triggers” are fired before and after data manipulation events. Their use is restricted at the database level. “Instead of Triggers” are types of triggers used to do some other actions than the action that invoked the trigger. They are normally used to update a view’s underlying table especially if the view is a join of two tables, as direct update via view will not be permitted. Triggers on System events and user events can also be used to publish information about database events to subscribers.

Triggers can be used to supplement oracle capabilities by providing a highly customized database management system that prevents invalid transactions, performs enhanced security authorization, enforces referential integrity , helps in audit by tracking data modifications ,automatically generate values for columns, maintain table replicates , gather statistics on table access, does updates on tables when data manipulation commands are executed on views etc . A point to remember is that excessive use of triggers can be difficult to maintain in large applications.

Further ways in which trigger can aid in customizing management information are, a trigger can help to monitor data loads by allowing data manipulation statements to execute only on regular business hours and help track and store the data modifications and deletion records with timestamp. Triggers can be defined to execute when a data definition event occurs, for example it can be used to execute procedure when any new data dictionary objects is added in the schema. It can be used to perform system maintenance functions on database events such as start up or shutdown. These maintenance functions for example, may involve effective ways of having PL/SQL objects in the shared pool of memory to improve performance and secure the database.

Oracle Database 10g auditing command helps to maintain audit trial but they capture changes that are rolled back also and do not show the values before and after the change. Using a trigger guarantees that only committed changes are captured and if desirable the values before and after changes can also be captured.

Triggers can play an important role in customizing applications in a database provided they are used judiciously. While designing triggers, care should be taken that triggers do not duplicate functions already built in oracle, use calls to procedure if lines of code exceeds certain limits, are used only for centralized and global operations that is regardless of user and application and are not recursive.

| About Sequences and their attributes in Oracle10g | Accessing Remote data with Database Links in Oracle10g | A Guide to Iterative Processing with Loops in PL/SQL | A note on Dynamic SQL and its implementation in PL/SQL Application | Autonomous Transactions in Oracle – How to create and use if efficiently | Backup and Recovery – Best Practices in Oracle10g | Compiling Procedures, Functions and Packages during Application Development in Oracle10g | Introducing Publish-Subscribe Model in Oracle10g | Exploring Debugging Procedures in Oracle10g | External Procedures and their uses in Oracle10g | Guidelines for Locking Data in Oracle10g | How to customize an oracle10g Database using Database triggers | Specifying Constraints while creating table in Oracle10g to enhance Data Integrity |