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

In Oracle PL/SQL, when you're dealing with object tables, the use of DML (Data Manipulation Language) statements like INSERT, UPDATE, and DELETE can have nuanced differences due to the nature of object types. Here's a detailed discussion on these differences:
INSERT
Basic Syntax for Object Tables:

• When inserting into an object table that contains columns of user-defined object types, you must specify the object type constructor explicitly. Here's an example:
  

  INSERT INTO object_table_name 
  VALUES (ObjectTypeConstructor(attr1 => 'value1', attr2 => 'value2'));
  

• Constructor Usage: The constructor for the object type must be used to form the object. If the object has attributes, you need to specify these in the constructor call.
  Example:
  

  CREATE TYPE address_type AS OBJECT (
      street VARCHAR2(50),
      city VARCHAR2(30),
      state CHAR(2)
  );
  
  CREATE TABLE employees_of (
      emp_id NUMBER PRIMARY KEY,
      emp_address address_type
  );
  
  INSERT INTO employees_of 
  VALUES (1, address_type('123 Main St', 'Anytown', 'CA'));
  

  
  Here, address_type is the constructor for the address_type object.

UPDATE

• Updating Object Attributes: When updating an object column, you need to reference the attribute of the object directly:
  

  UPDATE employees_of
  SET emp_address.city = 'Newtown'
  WHERE emp_id = 1;
  

• Complete Object Replacement: If you want to update the entire object, you'll again use the constructor:

  UPDATE employees_of
  SET emp_address = address_type('456 Elm St', 'Newtown', 'NY')
  WHERE emp_id = 1;
  

• Nested Objects: If the object type includes other objects, you might need to use dot notation to access nested attributes:

  UPDATE employees_of
  SET emp_address.street = '789 Pine Ave'
  WHERE emp_id = 1;
  

DELETE

• Deleting from object tables works similarly to standard tables without additional syntax for object types:
  

  DELETE FROM employees_of
  WHERE emp_id = 1;
  

• However, if you're dealing with nested tables or VARRAYs within your objects, deletion might involve more complex conditions to specify which nested elements to remove.

Key Points:

• Type Constructor: Always use the constructor when inserting or updating to ensure the object is correctly instantiated with the right structure.
• Attribute Access: For updates, you can directly access and modify individual attributes or replace the whole object.
• Performance Considerations: With object tables, especially those with complex nested structures, operations might be slower due to the overhead of handling object data compared to simpler column types.
• SQL vs. PL/SQL: While SQL statements work for basic operations, PL/SQL can offer more control and functionality, like handling exceptions or using PL/SQL-specific constructs when manipulating object tables.

Understanding these nuances helps in designing and manipulating data in Oracle databases where object-oriented features are utilized, ensuring data integrity and performance optimization.
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.

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:

Conditional Predicate	TRUE if and only if:
INSERTING	An INSERT statement fired the trigger.
UPDATING	An UPDATE statement fired the trigger.
UPDATING ('column')	An UPDATE statement that affected the specified column fired the trigger.
DELETING	A DELETE statement fired the trigger.

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.