6.2 Before versus After


The insert of a single row has three stages. Figure 6-1 illustrates each stage. There are two stages in which you can inject your PL/SQL code. The middle stage is the Oracle constraint enforcement. The first stage is the execution of the BEFORE-INSERT trigger. The trigger code at this stage runs BEFORE Oracle's constraint enforcement and is called a BEFORE-INSERT-ROW (BIR) trigger. You can write code that executes AFTER Oracle's constraint enforcement. This is the AFTER-INSERT-ROW (AIR) trigger.

Figure 6-1. Row Insert Trigger.

graphics/06fig01.gif

From an initial look at Figure 6-1 it seems that you can choose to perform an operation that executes before constraint enforcement or after the enforcement. In general, this is true.

Depending on what you want to accomplish you may be required to put your code in a before-constraint trigger, the first stage. For example, a trigger code that " overrides column" values must execute in a BEFORE-INSERT row trigger. In some situations, you may have a choice to use a before or after row trigger, but choose the after trigger because you want your code to execute after Oracle's constraint enforcement.

Each trigger in Figure 6-1 has the capability to "see the insert statement column values." This is a valuable feature. This feature allows a row trigger, for example, to raise an error if a column value violates a business rule.

Figure 6-1 shows the possible tasks that can be performed ( Tasks Performed) for the various stages of the insert statement. There are three stages: BEFORE-INSERT row trigger execution, Oracle constraint enforcement, and AFTER-INSERT row trigger execution. The action taken by Oracle's constraint enforcement is to raise an error or accept the data. The Task Performed by application developed triggers falls into three general categories:

  • Override Column (Before Row Trigger only)

  • Reject Transaction (Before and After Trigger)

  • Take Action (Before or After Trigger)

"Override Column" refers to a trigger changing the content of a value from the INSERT statement. For example, a BIR trigger can truncate a value for a DATE column. This means that an INSERT statement that uses SYSDATE in the statement will have that value truncated when it is inserted in the database.

"Reject the Transaction," in Figure 6-1, refers to the application enforcement of a complex rule. A complex rule is any rule that is not easily or possibly enforced with an Oracle CHECK constraint and can only be enforced procedurally. A trigger rejects an insert by calling the built-in function RAISE_APPLICATION_ERROR.

"Take Action," in Figure 6-1, refers to any action the trigger may take. This includes a print with DBMS_OUTPUT for debug purposes. Row triggers can send signals to other processes using the Oracle DBMS_ALERTS package. Row triggers sometimes populate global temporary tables with before and after row values for later use by after statement level triggers. Chapter 7 continues with this topic in Section 7.4, "Processing Row Captured Data."



Programming Oracle Triggers and Stored Procedures
Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)
ISBN: 0130850330
EAN: 2147483647
Year: 2003
Pages: 111
Authors: Kevin Owens

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net