OCL3 Oracle 10g: SQL

1
OCL3 Oracle 10gSQL PL/SQLSession 9

• Matthew P. Johnson
• CISDD, CUNY
• June, 2005

2
Agenda

• Triggers
• Transactions
• Oracles bulk loader
• Go over some labs
• Do some more labs

3
New topic 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 test of that update value)
• 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 (in Oracle, not in
  others)
• It may be triggered by
• INSERTs
• UPDATEs
• DELETEs

7
Trigger form
CREATE OR REPLACE TRIGGER trigger name BEFORE
AFTER INSTEAD OF 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

• 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
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
• AFTER UPDATE OF data ON R
• REFERENCING
• NEW ROW AS NewTuple
• FOR EACH ROW
• BEGIN
• NewTuple.last-modified sysdate
• END

13
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

14
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
15
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
16
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
17
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
18
DDL trigger e.g.

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

19
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
20
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
21
DB event restrictions

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

22
DB Trigger e.g.

• Gather stats before shutdown
• Log error messages

CREATE OR REPLACE TRIGGER on_shutdown BEFORE
SHUTDOWN ON DATABASE BEGIN gather_system_stats
END
23
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'

24
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

25
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

26
Intermission

• Go over previous labs
• Begin lab
• Break

27
New-old topic 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

28
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

29
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

30
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
31
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

32
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

33
AUTOCOMMIT

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

34
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

35
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)
36
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

37
The control file

• Example control file

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

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

1foo 2bar 3 baz
39
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
40
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
41
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
42
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
43
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
44
Lecture 10

• regexps
• Web apps/security
• Data warehousing extensions
• XML