Introduction to Oracle 10g Chapter 4 Modifying Data and Auditing Table Operations James Perry and Gerald Post 4-1 2007 by Prentice Hall Chapter Outline Inserting Rows into Tables Updating Data
Deleting Rows and Truncating Tables Merging Rows Database Transactions Creating and Using Database Triggers 4-2 2007 by Prentice Hall Table 4.1 Constraints and structure of the Agents table Column Description Data type Constraint(s) AgentID
Unique number assigned to each agent INTEGER Primary key Gender Agents sex NVARCHAR2(1 0) Check: only M, m, F or f Title Internal title
assigned to each employee NVARCHAR2(2 0) Check: only salesperson or broker (any combination of upper- and lowercase letters allowed) LicenseStatusI D Code representing an agents real estate license status
INTEGER Foreign key; value must be found in the LicenseStatus tables primary key column, LicenseStatusID 4-3 2007 by Prentice Hall Table 4.2 Common date format model symbols 4-4 Format Model Symbol Displayed or Input Value MONTH
Month MM FEBRUARY February 02 DD 15 DDD 251 DAY Day DY WEDNESDAY
Wednesday WED YYYY YY 2005 05 2007 by Prentice Hall Table 4.3 Oracles relational operators 4-5 Relational Operator Meaning
= Equal to <> or != Not equal to > Greater than < Less than <= Less than or equal to
>= Greater than or equal to 2007 by Prentice Hall Table 4.4 Oracles logical operators Logical Operator Meaning WHERE Clause Example AND True only if both conditions are true; false otherwise
WHERE State = MN AND Gender = M OR True if either condition is true; false otherwise WHERE LicenseStatusID = 1001 OR LicenseStatusID = 1002 NOT Negate expression WHERE NOT State = NE IN
True if among set of discrete values listed WHERE City IN(Arcata, Fortuna, Orick) LIKE Wildcard WHERE LastName LIKE Mc% expression allowing dont care conditions BETWEEN AND True if within the value range, inclusive
4-6 WHERE SqFt BETWEEN 1500 AND 2000 2007 by Prentice Hall Table 4.5 Annual bonus schedule based on employment longevity Length of service (months) 12 or less 4-7 Annual bonus amount $0 13 to 24
$500 25 to 48 $700 49 to 72 $1,000 73 or greater $1,500 2007 by Prentice Hall Table 4.6 Values supplied in trigger body by correlation names NEW and OLD SQL
Statement Correlation Name INSERT NEW Value supplied for the column in the statement that originated the transaction. OLD NULL NEW Value supplied for the column in the statement that originated the transaction.
OLD Value of the column that was last committed into the table prior to the transaction. NEW NULL OLD Value of the column that was last committed into the table before the transaction. UPDATE DELETE 4-8
Value 2007 by Prentice Hall Table 4.7 Columns available in the user_triggers data dictionary view 4-9 Column name Data type Meaning Trigger_Name VARCHAR2(30)
Name of trigger Trigger_Type VARCHAR2(16) Type of trigger Triggering_Event VARCHAR2(227) Event that causes trigger to fire Table_Owner VARCHAR2(30) User who owns the table that the trigger references
Base_Object_Typ e VARCHAR2(16) Type of object referenced by the trigger Table_Name VARCHAR2(30) Table referenced by the trigger Column_Name VARCHAR2(4000 ) Column referenced by the trigger
Referencing_Na mes VARCHAR2(128) Name of the OLD and NEW aliases. When_Clause VARCHAR2(4000 ) Trigger condition WHEN clause Status VARCHAR2(8) Whether the trigger is enabled or disabled
Description VARCHAR2(4000 ) Description of trigger Action_Type VARCHAR2(11) Action type of the trigger Trigger_Body LONG Code contained in trigger body 2007 by Prentice Hall
4.1 Structure of two related Redwood Realty tables Agents table AgentID LicenseStatus table (primary key) FirstName LicenseStatusID (primary key) StatusText LastName HireDate BirthDate
Gender WorkPhone CellPhone HomePhone Title TaxID LicenseID LicenseDate LicenseExpire LicenseStatusID 4-10 (foreign key) 2007 by Prentice Hall 4.2 Inserting rows into the Agents table 4-11
2007 by Prentice Hall 4.3 Correcting an integrity constraint violation 4-12 2007 by Prentice Hall 4.4 Displaying the contents of the LicenseStatus table 4-13 2007 by Prentice Hall 4.5 Displaying selected columns from the Agents table 4-14 2007 by Prentice Hall
4.6 Inserting date values into a table 4-15 2007 by Prentice Hall 4.7 Inserting rows from another table 4-16 2007 by Prentice Hall 4.8 Creating a sequence and displaying its characteristics 4-17 2007 by Prentice Hall 4.9 Reviewing sequence values and CURRVAL
4-18 2007 by Prentice Hall 4.10 Updating multiple columns in a single row 4-19 2007 by Prentice Hall 4.11 Updating multiple rows with a single expression 4-20 2007 by Prentice Hall 4.12 Selected Agents rows before being updated 4-21
2007 by Prentice Hall 4.13 Selected Agents rows after updating them 4-22 2007 by Prentice Hall 4.14 Running a SQL statement containing substitution variables 4-23 2007 by Prentice Hall 4.15 Processing substitution variables 4-24
2007 by Prentice Hall 4.16 Displaying updated table 4-25 2007 by Prentice Hall 4.17 Deleting selected rows from a table 4-26 2007 by Prentice Hall 4.18 Truncating the Agents table 4-27 2007 by Prentice Hall
4.19 MERGE example UpdateLicenseStatus source table 1004 1005 1101 1105 Passed Away Expired--Fee Not Paid License Probationary License Pending MERGE LicenseStatus target table 1001 1002 1003 1004 1005 1006
1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 4-28 Licensed Licensed NBA Canceled Officer Deceased Expired Government Service Military Service
Conditional Suspension Restricted Revoked Flag Suspended Voided Withheld Denied 17520 FC Suspended 11350.6 W and I Suspended Surrendered LicenseStatus table following MERGE 1001 1002 1003 1004 1005 1006 1007 1008 1009
1010 1011 1012 1013 1014 1015 1016 1101 1105 Licensed Licensed NBA Canceled Officer Passed Away Expired--Fee Not Paid Government Service Military Service Conditional Suspension Restricted Revoked
Flag Suspended Voided Withheld Denied 17520 FC Suspended 11350.6 W and I Suspended Surrendered License Probationary License Pending updated columns (bold) Inserted rows (bold) 2007 by Prentice Hall 4.20 Issuing MERGE to modify a table 4-29
2007 by Prentice Hall 4.21 Illustration of transactions Transaction Transaction R D O P E BL
TA RT SE IN T ; IT M M R SE
IN O C T ; E AT PD U R SE
IN E ET EL D T IT M M R SE IN O C
Time 4-30 2007 by Prentice Hall 4.22 Using savepoints in a transaction creating a savepoint creating a savepoint creating a savepoint change rolled back to latest savepoint 4-31 2007 by Prentice Hall
4.23 Modifying the Agents table 4-32 2007 by Prentice Hall 4.24 Ending a transaction with COMMIT 4-33 2007 by Prentice Hall 4.25 Creating a sequence and a BEFORE INSERT trigger 4-34 2007 by Prentice Hall 4.26 Displaying Agents rows with trigger-supplied primary
key values 4-35 2007 by Prentice Hall 4.27 Creating an audit table and an AFTER UPDATE trigger 4-36 2007 by Prentice Hall 4.28 Displaying the audit table 4-37 2007 by Prentice Hall 4.29 A trigger execution error
4-38 2007 by Prentice Hall 4.30 Correctly executing a statement-level trigger Output produced by statement-level trigger. 4-39 2007 by Prentice Hall 4.31 Displaying trigger information 4-40 2007 by Prentice Hall
