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:
Define important concepts for modifying object tables
Write SQL to insert a row into an object table
Insert rows into sub queries
Write SQL to update object tables
Delete rows using SQL
Conditional Predicates for Detecting Triggering DML Statement
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
UPDATE OF salary, department_id OR
WHEN INSERTING THEN
WHEN UPDATING('salary') THEN
WHEN UPDATING('department_id') THEN
DBMS_OUTPUT.PUT_LINE('Updating department ID');
WHEN DELETING THEN
In this module, you were introduced to the following glossary term:
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.