Lesson 8

This module discussed the subtle differences in the use of DML statements such as INSERT, UPDATE, and DELETE when it comes to object tables. For example, if an object table contains a column that is associated with an object type, you must specify the object type name to insert or update the records. When updating a record within an object table, you must use table name aliases. In the case of object tables that contain references to other objects, you must use the reference (REF) operator to insert and update the records. Now that you have completed this module, you should be able to:

1. Define important concepts for modifying object tables
2. Write SQL to insert a row into an object table
3. Insert rows into sub queries
4. Write SQL to update object tables
5. Delete rows using SQL

The triggering event of a DML trigger can be composed of multiple triggering statements. When one of them fires the trigger, the trigger can determine which one by using these conditional predicates:

A conditional predicate can appear wherever a BOOLEAN expression can appear. Example 5-8 creates a DML trigger that uses conditional predicates to determine which of its four possible triggering statements fired it.
Example 5-8 Trigger Uses Conditional Predicates to Detect Triggering Statement

CREATE OR REPLACE TRIGGER t
BEFORE
INSERT OR
UPDATE OF salary, department_id OR
DELETE
ON employees
BEGIN
CASE
WHEN INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserting');
WHEN UPDATING('salary') THEN
DBMS_OUTPUT.PUT_LINE('Updating salary');
WHEN UPDATING('department_id') THEN
DBMS_OUTPUT.PUT_LINE('Updating department ID');
WHEN DELETING THEN
DBMS_OUTPUT.PUT_LINE('Deleting');
END CASE;
END;
/

In this module, you were introduced to the following glossary term:

1. Data Manipulation Language: INSERT, UPDATE, and DELETE statements are DML statements.

When a DML operation is performed, the primary key constraint is enforced using this existing index. In the next module, you will learn the different ways to modify data within object tables with varrays and nested tables. You will also learn how to insert, update, and delete records from object tables that are based on varray or nested table.