Using triggers and virtual columns

In this recipe, we will see how to use virtual columns, a new feature in Oracle Database 11g, to avoid the use of DML triggers, resulting in a performance gain in our applications.

Note

Virtual columns can also be used in referential integrity, tables can be partitioned by them, and statistics can be gathered on them.

How to do it...

The following steps will demonstrate the use of virtual columns:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create a table and call it LOANS:

   CREATE TABLE sh.LOANS (
     LOAN_ID INT NOT NULL,
     PAYMENT NUMBER,
     NUMBER_PAYMENTS NUMBER,
     GROSS_CAPITAL NUMBER);
   

3. Create a trigger on the LOANS table to calculate the GROSS_CAPITAL field, giving the number of payments and the amount of every single payment:

   CREATE OR REPLACE TRIGGER TR_LOANS_INS
     BEFORE UPDATE OR INSERT ON sh.LOANS
     FOR EACH ROW
   BEGIN
     :new.GROSS_CAPITAL := :new.PAYMENT * :new.NUMBER_PAYMENTS;
   END;
   /
   

4. Insert several rows in the LOANS table and query against it, measuring...