Database Triggers

A trigger - like a stored procedure - is a subprogram that does not return a value. The distinguishing characteristic of a trigger is that it is not called; rather it is fired in response to a triggering event. When a trigger is fired the PL/SQL code in the body of the trigger is executed.

In this lesson you will learn how to create, drop and modify triggers to change their functionality. You will also learn how to view triggers within the database and obtain information concerning their current state. Because triggers are distinguished by the fact that they fire in response to an event, there are a few other operations unique to trigger maintenance. These include the ability to disable triggers to prevent them from firing without removing them from the database. Triggers that have been disabled can later be enabled to make them functional again.

Purpose of Triggers

There are a number of situations when you might want to use a trigger rather than another PL/SQL program type. A trigger can generate values for columns, provide validation (prevent invalid data, enforce security or referential integrity), implement specific business rules, provide auditing or logging (including replicating data), modify table data when views have DML run against them, or publish information to external applications. Keep in mind, though, that triggers should not be used as a replacement for what can be easily accomplished with database constraints; trigger performance is slower than database constraint enforcement.

Triggers fire within the scope of a database transaction. You need to take special care when designing and using triggers to prevent problems with performance and application support. Because triggers fire without any explicit notice, their effects can be confusing. In addition, interactions between triggers can result in side effects that result in errors or situations that are difficult to debug.

There are a number of situations when you might want to use a trigger rather than another PL/SQL program type. A trigger can generate values for columns, provide validation (prevent invalid data, enforce security or referential integrity), implement specific business rules, provide auditing or logging (including replicating data), modify table data when views have DML run against them, or publish information to external applications. Keep in mind, though, that triggers should not be used as a replacement for what can be easily accomplished with database constraints; trigger performance is slower than database constraint enforcement.

Triggers fire within the scope of a database transaction. You need to take special care when designing and using triggers to prevent problems with performance and application support. Because triggers fire without any explicit notice, their effects can be confusing. In addition, interactions between triggers can result in side effects that result in errors or situations that are difficult to debug.

Contact Us or call 1-877-932-8228