OCL1 Oracle 10g: SQL

1
OCL1 Oracle 10gSQL PL/SQLSession 9

• Matthew P. Johnson
• CISDD, CUNY
• Fall, 2004

2
Agenda

• Triggers
• Constraints
• Transactions
• Oracles bulk loader
• PL/SQL lab
• Todays lab

3
What are triggers?

• PL/SQL programs that run automatically (are
  triggered) when a certain event occurs
• E.g. on insert to some table
• On system start-up
• On delete from table
• Big benefit need not be called explicitly
• However row in table x is deleted, the trigger
  gets called

4
Trigger events

• Trigger code may be triggered by many kinds of
  events
• Oracle start-up/shut-down
• Triggers may replace initialization scripts
• Data updates
• Delete maybe delete related rows
• Inserts
• Updates maybe make other rows consistent
• Delete maybe prevent
• DDL statements
• Log creation of all objects, e.g.

5
Triggers

• Constraints state what must remain true
• DBMS decides when to check
• Triggers are instructions to perform at
  explicitly specified times
• Three aspects
• An event (e.g., update to an attribute)
• A condition (e.g., a query to check)
• An action (the triggers effect) (deletion,
  update, insertion)
• When the event occurs, DBMS checks the
  constraint, and if it is satisfied, performs the
  action

6
DML trigger options

• The trigger may be
• Statement-level (e.g., a DELETE WHERE statement)
  or
• Row-level (e.g., for each row deleted)
• The trigger may run
• BEFORE
• AFTER or
• INSTEAD OF the statement
• It may be triggered by
• INSERTs
• UPDATEs
• DELETEs

7
Trigger form
CREATE OR REPLACE TRIGGER trigger name BEFORE
AFTER INSERT DELETE UPDATE UPDATE OF
column list ON table name FOR EACH ROW WHEN
(...) DECLARE ... BEGIN ... executable
statements ... EXCEPTION ... END trigger
name
8
Trigger type examples

• First run copy_tables.sql
• statement_vs_row.sql
• INSERT INTO to_table SELECT FROM from_table
• before_vs_after.sql
• INSERT INTO to_table SELECT FROM from_table
• one_trigger_per_type.sql
• INSERT INTO to_table VALUES (1)
• UPDATE to_table SET col1 10
• DELETE to_table

9
DML Trigger e.g.
CREATE OR REPLACE TRIGGER validate_employee_change
s BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW BEGIN check_age
(NEW.date_of_birth) check_resume
(NEW.resume) END

• Q Why is this (maybe) better than client-side
  validation?

10
Triggers with WHEN
CREATE OR REPLACE TRIGGER check_raise AFTER
UPDATE OF salary, commission ON employee
FOR EACH ROW WHEN ((OLD.salary ! NEW.salary OR
(OLD.salary IS NULL AND NEW.salary IS
NULL)) OR (OLD.commission ! NEW.commission OR
(OLD.commission IS NULL AND NEW.commission IS
NULL))) BEGIN ... END

• NB WHEN applies only to row-level triggers

11
Triggers with WHEN

• Remember WHEN applies only to row-levels
• Parentheses are required
• Can only call built-in functions in when
• Packages like DBMS_OUTPUT are not allowed

CREATE OR REPLACE TRIGGER valid_when_clause BEFOR
E INSERT ON frame FOR EACH ROW WHEN (
TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 )
...
12
Multiple DML actions

• DML actions may be ORed together

CREATE OR REPLACE TRIGGER three_for_the_price_of_
one BEFORE DELETE OR INSERT OR UPDATE ON
account_transaction FOR EACH ROW BEGIN IF
INSERTING THEN NEW.created_by USER
NEW.created_date SYSDATE ELSIF DELETING
THEN audit_deletion(USER,SYSDATE) END

• To find actual action, check
• INSERTING
• DELETING
• UPDATING

13
More on UPDATING

• UPDATING may be called for partic. columns

CREATE OR REPLACE TRIGGER validate_update BEFORE
UPDATE ON account_transaction FOR EACH ROW BEGIN
IF UPDATING ('ACCOUNT_NO') THEN
errpkg.raise('Account number cannot be
updated') END IF END
14
Extended auditing example

• Tables grades, grades_audit
• Run grades_tables.sql, grades_audit.sql
• Cases hacker changes grades, deletes others

UPDATE grades SET grade 'A' WHERE student_id
1 AND class_id 101
DELETE grades WHERE student_id 2 AND class_id
101
15
Extended auditing example

• Run grades_tables.sql, grades_audit2.sql
• Cases hacker changes student or class ids

UPDATE grades SET student_id 3 WHERE student_id
1 AND class_id 101 UPDATE grades SET
student_id 1 WHERE student_id 2 AND class_id
101 UPDATE grades SET student_id 2 WHERE
student_id 3 AND class_id 101
16
DDL Triggers

• Respond to DDL events
• Creating/dropping tables, indices, etc.
• ALTER TABLE etc.
• General form

CREATE OR REPLACE TRIGGER trigger name BEFORE
AFTER DDL event ON DATABASE
SCHEMA DECLARE Variable declarations BEGIN ...
some code... END
17
DDL trigger e.g.

• Town crier examples triggered by creates
• uninformed_town_crier.sql
• informed_town_crier.sql

18
Available DDL events

• CREATE, ALTER, DROP, GRANT, RENAME, REVOKE,
  TRUNCATE
• DDL any DDL event
• Q Does this work??

CREATE OR REPLACE TRIGGER no_create AFTER
CREATE ON SCHEMA BEGIN RAISE_APPLICATION_ERROR
(-20000, 'ERROR Objects cannot be created in
the production database.') END
19
DB Event triggers

• Form similar to DDL triggers
• Triggering events STARTUP, SHUTDOWN,
  SERVERERROR, LOGON, LOGOFF

CREATE OR REPLACE TRIGGER trigger name BEFORE
AFTER database event ON DATABASE
SCHEMA DECLARE Variable declarations BEGIN ...
some code... END
20
DB event restrictions

• Have BEFORE and AFTER as above, but they dont
  always apply
• No BEFORE STARTUP/LOGON/SERVERERROR
• No AFTER SHUTDOWN/LOGOFF

21
DB Trigger e.g.

• Gather stat before shutdown
• Log error messages error_log.sql

CREATE OR REPLACE TRIGGER on_shutdown BEFORE
SHUTDOWN ON DATABASE BEGIN gather_system_stats
END
22
Trigger maintenance

• Enabling disabling
• ALTER TRIGGER emp_after_insert DISABLE
• ALTER TRIGGER emp_after_insert ENABLE
• Deleting
• DROP TRIGGER emp_after_insert
• Viewing
• select trigger_name from user_triggers
• select text from user_source where
  name'TOWN_CRIER'
• Check validity
• select object_name, status from user_objects
  where object_type'TRIGGER'

23
Triggers important points

• Can replace old row (result of event) with new
  row
• Action may be performed before or after event
• Can refer to old row and new row
• WHEN clauses tests whether to continue
• Action may be performed either
• For each row involved in event
• Once per event
• Oracle does triggers as PL/SQL programs
• A trigger runs in the same transaction as the
  event triggering it

24
Elements of Triggers

• Timing of action execution before, after or
  instead of triggering event
• The action can refer to both the old and new
  state of the database
• Update events may specify a particular column or
  set of columns
• A condition is specified with an optional WHEN
  clause
• The action can be performed either for
• once for every tuple or
• once for all the tuples that are changed by the
  database operation

25
Simple trigger example

• R(id, data, last-modified)
• data is a large string
• Last-modified is a newly added date field
• Goal whenever data is modified, update
  last-modified date
• Could modify all scripts/programs that touch this
  table
• Bad idea
• Better user a trigger

• CREATE TRIGGER UpdateDateTrigger
• BEFORE UPDATE OF data ON R
• REFERENCING
• NEW ROW AS NewTuple
• FOR EACH STATEMENT
• BEGIN
• NewTuple.last-modified sysdate
• END

26
Triggers Row-level example

• MovieExec(name, address, cert, netWorth)
• If someone decreases a movie executives net
  worth, I want the database to reset itself to the
  previous net worth.

CREATE TRIGGER NetWorthTrigger AFTER UPDATE OF
netWorth ON MovieExec REFERENCING NEW ROW AS
NewTuple OLD ROW AS OldTuple FOR EACH ROW WHEN
(OldTuple.netWorthgtNewTuple.netWorth) UPDATE
MovieExec SET netWorth oldTuple.netWorth WHERE
cert newTuple.cert)
27
Triggers Table-level example

• MovieExec(name, address, cert, netWorth)
• If someone updates the net worth of one movie
  exec so that the average net worth of all movie
  execs becomes less than 50,000, I want the
  database to reset itself.

CREATE TRIGGER AvgNetWorthTrigger AFTER UPDATE OF
netWorth ON MovieExec REFERENCING OLD TABLE AS
OldStuff, NEW TABLE AS NewStuff FOR EACH
STATEMENT WHEN (50000 gt (SELECT AVG(netWorth)
FROM MovieExec)) BEGIN DELETE FROM
MovieExec WHERE (Name, address, cert, netWorth)
IN NewStuff INSERT INTO MovieExec (SELECT
FROM OldStuff) END
28
Mutating Table Errors

• Its generally impossible to a row-level trigger
  to modify or query the table that triggered it
• Does not apply to statement-level triggers
• Can do the equiv by creating a complicated
  statement-level trigger
• Wont discuss

29
Intermission

• Go over previous labs
• Begin lab
• Break

30
Transactions

• So far, have simply issued commands
• Ignored xacts
• Recall, though an xact is an operation/set of
  ops executed atomically
• In one instant
• ACID test
• Xacts are atomic
• Each xact (not each statement) must leave the DB
  consistent

31
Default xact behavior

• An xact begins upon login
• By default, xact lasts until logoff
• Except for DDL statements
• They automatically commit
• Examples with two views of emp

32
Direct xact instructions

• At any point, may explicitly COMMIT
• SQLgt COMMIT
• Saves all statements entered up to now
• Begins new xact
• Conversely, can ROLLBACK
• SQLgt ROLLBACK
• Cancels all statements entered since start of
  xact
• Example delete from emp or delete junk

33
Direct xact instructions

• Remember, DDL statements are auto-committed
• ? They cannot be rollbacked
• Examples
• Q Why doesnt rollback work?

drop table junk rollback
truncate table junk rollback
34
Savepoints

• Xacts are atomic
• Can rollback to beginning of current xact
• But might want to rollback only part way
• Make 10 changes, make one bad change
• Want to roll back to before last change
• Dont have Word-like multiple undo
• But do have savepoints

35
Savepoints

• Create a savepoint
• emp example

SAVEPOINT savept_name
--changes SAVEPOINT sp1 --changes SAVEPOINT
sp2 --changes SAVEPOINT sp3 --changes ROLLBACK
TO sp2 ROLLBACK TO sp1

• Can skip savepoints
• But can ROLLBACK only backwards
• Can ROLLBACK only to last COMMIT

36
AUTOCOMMIT

• Finally, can turn AUTOCOMMIT on
• SQLgt SET AUTOCOMMIT ON
• Then each statement is auto-committed as its own
  xact
• Not just DDL statements

37
New topic the bulk loader

• To insert data, can insert rows one at a time
  with INSERT INTO lttablegt VALUES(ltgt)
• If data is in/can be computed from other tables,
  can use
• INSERT INTO lttablegt SELECT
• Often, have text file of data
• Oracles bulk loader will parse file and insert
  all into the database

38
Using the bulk loader

• The bulk loader takes two files
• The data file
• The control file, specifying how to load the data
• Control file form

LOAD DATA INFILE ltdataFilegt ltAPPENDgt INTO TABLE
lttableNamegt FIELDS TERMINATED BY
'ltseparatorgt' (ltlist of all attribute names to
loadgt)
39
The control file
LOAD DATA INFILE ltdataFilegt ltAPPENDgt INTO TABLE
lttableNamegt FIELDS TERMINATED BY
'ltseparatorgt' (ltlist of all attribute names to
loadgt)

• Default data file extension .dat
• Default control file extension .ctl
• If APPEND is omitted, the table must be empty,
  else error
• Attribute list is comma-separated, but order
  doesnt matter
• Separator can be multi-char

40
The control file

• Example control file

LOAD DATA INFILE test.dat INTO TABLE test FIELDS
TERMINATED BY '' (i, s)
41
The data file

• Plain text file
• Each line ? one row in the table
• Example data file

1foo 2bar 3 baz
42
Running the bulk loader

• The bulk loader is a command-line program sqlldr,
  separate from SQLPlus
• At cmd line, specify
• user/pass (pass is optional here)
• the control file (which specifies data file), and
• (optionally) a log file (dft ext .log)
• (optionally) a bad file (dft ext .bad)

c\ sqlldr scott/tiger controltest logtest
badbad
43
Data in the control file

• Can also merge the data and control file
  (onefile.ctl)
• The indicates that the data is in this file

LOAD DATA INFILE INTO TABLE test FIELDS
TERMINATED BY '' (i, s) BEGINDATA 1foo 2bar 3
baz
44
Loading dates

• In the control files attribute list, follow a
  data field with a date mask
• Date masks are case-INsensitive and include
• d - day
• m - month
• y - year
• withdates.ctl

LOAD DATA INFILE INTO TABLE foo FIELDS
TERMINATED BY '' (i, d DATE 'dd-mm-yyyy') BEGINDA
TA 101-01-1990 24-1-1998
45
Loading timestamps

• Similar to dates but includes more chars
• Mi minutes
• ss seconds
• hh hour
• hh24 24-hour hour
• ff millisecond (fractional seconds)
• withtimestamps.ctl

LOAD DATA INFILE APPEND INTO TABLE ts FIELDS
TERMINATED BY ',' (s, t timestamp
'yyyymmddhh24miss.ff' ) BEGINDATA 1,20041012081522
.123 1,10661012081522.321
46
Loading nulls
35 24 16 7

• Two adjacent separators are interpreted as a null
  value in the field
• What if null in last field?
• Two options
• Put TRAILING NULLCOLS following field-term df
• Append an extra field sep to end
• withnulls.ctl

LOAD DATA INFILE APPEND INTO TABLE nums FIELDS
TERMINATED BY '' TRAILING NULLCOLS
(a,b,c) BEGINDATA 35 24 12 12
47
After

• Complete lab
• Read XML and regex chapters for next time
• Vote early and vote often!