Introduction to Oracle 10g - Pearson Education

Introduction to Oracle 10g - Pearson Education

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

Recently Viewed Presentations

  • Top 10 ways to change your brain - Oregon Counseling

    Top 10 ways to change your brain - Oregon Counseling

    Rhythm-natural body rhythms, entrainment . Frequency- high rich and stimulation, harmonies . Medium- voice and speech - low= hearing and movement . Melody -combination of tones- voice . Tempo- # beats- heart rate, breathing, brain waves . Music
  • ArcGIS Server 9.3 Flex API - University of Missouri

    ArcGIS Server 9.3 Flex API - University of Missouri

    What is Actionscript? Common Descendent of ECMAScript (originally JavaScript), the web scripting standard. Syntax is C-like, but easier to work with then a full programming language Flex and ActionScript The programmer develops highly-interactive web sites by using the Flex framework,...
  • Defense Acquisition Regulations System (DARS): The Rule-Making Process

    Defense Acquisition Regulations System (DARS): The Rule-Making Process

    Committee Report with Draft Rule . GSA Legal Review. OFPP Review. Public Comment. Period (60 days) Cmte Rpt - Eval of Public. Comments/Draft Final Rule. OFPP Review. FAR Final Rule Published. OIRA Clearance. FAR Case Timeline. DARC/CAAC Review (60 days)...
  • Variables and Control - East Tennessee State University

    Variables and Control - East Tennessee State University

    Variables and Control Basic Principles of Research Design Overview of Tonight's Lecture Variables and Control 2 variables we can control (subject + situation) Research designs are rules about controls and variables evaluated in terms of Validity (answers the question set...
  • Los Estudios Culturales y la Comunicología.

    Los Estudios Culturales y la Comunicología.

    Bibliografía: CURRAN James, Morley David, Walkerdine, Valerie (compiladores) (1998). Estudios culturales y comunicación. Análisis, producción y consumo cultural de las políticas de identidad y el posmodernismo.
  • Level 4 Mine Planner Alastair Macfarlane Purpose of

    Level 4 Mine Planner Alastair Macfarlane Purpose of

    Level 4 Mine Planner. ... www.minerpsolutions.com. To brief industry and employers of the progress of the development of the L4 qualification. ... No career pathway in Mine Planning. External demands for more competency: Samrec. Samval.
  • TD Government Solutions

    TD Government Solutions

    Compliant Allocations: Getting Costs To Where They Belong (Includes the Magic Circle Authored by Charles Wilkins) Contract Cost Principles FAR Subpart 31.2 Cost Allowability. What is Certified Cost or Price Data. Truth In Negotiation Act (TINA) Defective Pricing. Cost Estimating...
  • Bringing Pop Culture into Our Classrooms

    Bringing Pop Culture into Our Classrooms

    Authentic Task: Create a newspaper dated 1933, Maycomb, AL using real historical events as well as the events in Harper Lee's To Kill a Mockingbird. Include all contents of Newspaper. Speech using persuasive techniques