Triggers

What are triggers?

A trigger defines an action the database should take when some database-related event (such as inserts, updates, deletes) occurs.

• Triggers are similar to procedures, in that they are named PL/SQL blocks.

Differences between Procedures and Triggers

A procedure is executed explicitly from another block via a procedure call with passing arguments, while a trigger is executed (or fired) implicitly whenever the triggering event (DML: INSERT, UPDATE, or DELETE) happens, and a trigger doesn't accept arguments.

When triggers are used?

• Maintaining complex integrity constraints (referential integrity) or business rules
• Auditing information in a table by recording the changes.
• Automatically signaling other programs that action needs to take place when changes are made to a table
• Collecting/maintaining statistical data.

CREATE [OR REPLACE] TRIGGER trigger_name
                    {BEFORE | AFTER} {INSERT|UPDATE|DELETE} [OF column]
                    ON table_reference
                   [FOR EACH ROW [WHEN trigger_condition]]

[DECLARE]                    -- optional, for declaring local variables

trigger_body;

• Note that the DECLARE keyword is back in Trigger!
• The Trigger_body is executed when an event (Insert, Update, Delete operation) occurs.

Trigger names

Triggers exist in a separate namespace from procedure, package, tables (that share the same namespace), which means that a trigger can have the same name as a table or procedure.

Types of triggers

There are two types of triggers in Oracle including row-level triggers and statement-level triggers

Row-level triggers for data-related activities

• Row-level triggers execute once for each row in a transaction.
• Row-level triggers are the most common type of triggers; they are often used in data auditing applications.
• Row-level trigger is identified by the FOR EACH ROW clause in the CREATE TRIGGER command.

Statement-level triggers for transaction-related activities

• Statement-level triggers execute once for each transaction. For example, if a single transaction inserted 500 rows into the    Customer table, then a statement-level trigger on that table would only be executed once.
• Statement-level triggers therefore are not often used for data-related activities; they are normally used to enforce additional    security measures on the types of transactions that may be performed on a table.
• Statement-level triggers are the default type of triggers created and are identified by omitting the FOR EACH ROW clause    in the CREATE TRIGGER command.

Before and After Triggers

• Since triggers occur because of events, they may be set to occur immediately before or after those events. The events that    execute triggers are database transactions, triggers can be executed immediately BEFORE or AFTER the statements    INSERTs, UPDATEs, DELETEs.
• AFTER row-level triggers are frequently used in auditing applications, since they do not fire until the row has been modified.
  Clearly, there is a great deal of flexibility in the design of a trigger.

Valid trigger types (possible combination of triggers)

• Statement (INSERT, DELETE, UPDATE), Timing (BEFORE, AFTER),
   Level (Row-level, Statement-level)
   The values for the statement, timing, and level determine the types of the triggers. There are total of 12 possible types of    triggers: 3*2*2 = 12

Combining Trigger Types

• Triggers for multiple INSERT, UPDATE, DELETE commands on a table can be combined into a single trigger (using OR),    provided they are all at the same level (row-level or statement-level), e.g., INSERT OR UPDATE OR DELETE.
• However, you can not combine BEFORE or AFTER, e.g., BEFORE OR AFTER is illegal.

An Example of Trigger                                       -- for the table major_stats(major, total_credits, total_students);

CREATE OR REPLACE TRIGGER updateMajorStats
    AFTER INSERT OR DELETE OR UPDATE ON students                   -- Oracle will check the status of this table

DECLARE                                                        -- unlike a procedure, use DECLARE keyword
    CURSOR c_statistics IS
                  SELECT major, COUNT(*) total_students, SUM(current_credits) total_credits
                  FROM students
                  GROUP BY major;
BEGIN
    FOR v_statsRecord IN c_statistics LOOP
         UPDATE major_stats
             SET total_credits = v_statsRecord.total_credits,
                     total_students = v_statsRecord.total_students
             WHERE major = v_statsRecord.major;

         IF SQL%NOTFOUND THEN
             INSERT INTO major_stats(major, total_credits, total_students)
             VALUES(v_statsRecord.major,
                              v_statsRecord.total_credits, v_statsRecord.total_students);
        END IF;
    END LOOP;
END updateMajorStats;

Using pseudo variable :old and :new in Row-level Triggers only

• A row-level trigger fires once per row processed by the triggering statement. Inside the trigger, you can access the row that is    currently being processed through two pseudocodes -- :old and :new, e.g., :new.field or :old.field. However, they are not    true records.
• :old refers to the data as it existed prior to the transaction. Updates and Deletes usually reference :old values. The :new values    are the data values that the transaction creates (such as the columns in an inserted record).
• :old is undefined for INSERT statements, :new is undefined for DELETE statements.
• If you need to set a column value in an inserted row via your trigger, then you will need to use a BEFORE INSERT trigger in   order to access the :new values. Using an AFTER INSERT trigger would not allow you to set the inserted value, since the row   will already have been inserted into the table (and thus be :old).
• The colon in front of :new and :old is required.
• Operations that would normally be valid on records are not valid for :new and :old. For example, they cannot be assigned    as entire records, e.g., v_tmprec := :old; is not legal. Only the individual fields within them may be assigned, e.g.,     v_tmprec.col := :old.col;.
• Likewise, :old and :new cannot be passed to procedures or functions that take arguments of triggering_table%ROWTYPE,   since the actual parameter is assigned to the formal parameter in the same way as a standard assignment.

For example, the GenerateStudentID trigger shown next uses :new. Its purpose is to fill in the ID field of Students with a vlaue generated from the student_sequence sequence.

CREATE OR REPLACE TRIGGER GenerateStudentID
            BEFORE INSERT OR UPDATE ON students
            FOR EACH ROW
BEGIN

            SELECT student_sequence.nextval INTO :new.ID
            FROM dual;

END GenerateStudentID;

The trigger GenerateStudentID actually modifies the value of :new.ID. This is one of the useful features of :new -- when the statement is actually executed, whatever values are in :new will be used.

INSERT INTO students(first_name, last_name) VALUES('Lolita', 'Lazarus');

We can issue an INSERT statement without generating an error. Even though we haven't specified a value for the primary column ID (which is required), the trigger will supply it.

=>  If we specify a value for ID, it will be ignored, since the trigger changes it.

The WHEN clause -- WHEN condition

• The WHEN clause is valid for row-level triggers only. If present, the trigger body will be executed only for those rows that    meet the condition specified by the WHEN clause.

For example, suppose we want to monitor any adjustments to an Amount that are greater than 10 percent.

CREATE TRIGGER customer_bef_upd_row
          BEFORE UPDATE ON customer
          FOR EACH ROW
WHEN (new.amount/old.amount > 1.1)                  --note that there is no colon “:” for “new” and “old”
                                                                               -- missing the colon is not an error.
BEGIN
            INSERT INTO customer_audit                 -- different table from the triggering table
            VALUES (:old.Action_date, :old.Action, :old.Item, :old.qty, :old.qtyType, :old.rate, :old.amount)
                                                                                -- the colon “:” is back in the trigger body.
END;

• The above row-level BEFORE UPDATE trigger will be executed only if the new value of the Amount column is more than 10    percent greater than its old value.
• The When clause adds further criteria to the triggering condition. The triggering event must not only be an UPDATE of the    customer table, but also must reflect an increase of over 10 percent in the value of the Amount column.
• The statements in the BEGIN and END is the trigger body. The commands shown in the BEGIN and END are to be executed    for every UPDATE of the customer table that passes the WHEN condition.

Using Trigger Predicates: INSERTING, UPDATING, and DELETING

We can use three boolean functions to determine what the operation is (since you can have several triggering operations, sometimes you need to determine which operation is performing now).

CREATE TRIGGER customer_bef_upd_ins_row
             BEFORE INSERT OR UPDATE OF Amount ON CUSTOMER
             FOR EACH ROW
BEGIN
             IF INSERTING THEN
               INSERT INTO customer_audit
                    VALUES(:new.Action_date, :new.Action, :new.Item, :new.qty, :new.qtyType,
                                                                                        :new.rate, :new.Amount);
             ELSE                                         -- if not inserting, then we are updating Amount
                 INSERT INTO customer_audit
                        VALUES(:old.Action_date, :old.Action, :old.Item, :old.qty, :old.qtyType,
                                                             :old.rate, :old.Amount);
             END IF;
END;

• If you look at the trigger body in the above PL/SQL program, the trigger checks to see if the record is being inserted into the   customer table. If it is, then the first part of the trigger body is executed. The INSERTING portion of the trigger body inserts   the new values of the record into the customer_audit table.
• Other transaction types can be checked. In this example, since the trigger executed, the transaction must either an INSERT or    an UPDATE of the Amount column.

Dictionary table for triggers

USER_TRIGGERS

Triggers and Dependencies

A trigger has a separate name space from a procedure/function or package, which means there is no naming conflict with a procedure. Since the trigger is stored in source format, it does not depend on anything.

Restrictions on Triggers

• A trigger may not issue any transaction control statement (e.g., COMMIT, ROLLBACK, …)
• The trigger body cannot declare any LONG variables.
• Row-level triggers may not access mutating or constraining tables. Mutating table is a table that the current trigger is watching    for a modification. Constraining table is a table that is related to a mutating table through foreign key.

Trigger related commands

• DROP TRIGGER trigger-name
• ALTER TRIGGER trigger-name [{DISABLE | ENABLE }]
• ALTER TABLE table_name [{DISABLE | ENABLE } [ALL TRIGGERS]]