Transcript PPT

OCL2 Oracle 10g:
SQL & PL/SQL
Session #9
Matthew P. Johnson
CISDD, CUNY
January, 2005
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
1
Agenda






Triggers
Constraints
Transactions
Oracle’s bulk loader
PL/SQL lab
Today’s lab
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
2
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
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
3
Trigger events


Trigger code may be “triggered” by many kinds of
events:
Oracle start-up/shut-down


Data updates:





Triggers may replace initialization scripts
Delete: maybe delete related rows
Inserts
Updates: maybe make other rows consistent
Delete: maybe prevent
DDL statements

Log creation of all objects, e.g.
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
4
Triggers

Constraints state what must remain true



Triggers are instructions to perform at explicitly
specified times
Three aspects:




DBMS decides when to check
An event (e.g., update to an attribute)
A condition (e.g., a test of that update value)
An action (the trigger’s effect) (deletion, update, insertion)
When the event occurs, DBMS checks the
constraint, and if it is satisfied, performs the action
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
5
DML trigger options

The trigger may be:



The trigger may run




Statement-level (e.g., a DELETE WHERE statement) or
Row-level (e.g., for each row deleted)
BEFORE
AFTER or
INSTEAD OF the statement (in Oracle, not in others)
It may be triggered by



INSERTs
UPDATEs
DELETEs
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
6
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];
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
7
Trigger type examples

First run copy_tables.sql
1.
statement_vs_row.sql

INSERT INTO to_table SELECT * FROM from_table;
before_vs_after.sql
2.

INSERT INTO to_table SELECT * FROM from_table;
one_trigger_per_type.sql
3.

INSERT INTO to_table VALUES (1);
UPDATE to_table SET col1 = 10;
DELETE to_table;
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
8
DML Trigger e.g.
CREATE OR REPLACE TRIGGER
validate_employee_changes
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?
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
9
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
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
10
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
BEFORE INSERT ON frame
FOR EACH ROW
WHEN ( TO_CHAR(SYSDATE,'HH24')
BETWEEN 9 AND 17 )
...
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
11
Multiple DML actions

DML actions may be ORed together
CREATE OR REPLACE TRIGGER
 To find actual
three_for_the_price_of_one
action, check: BEFORE DELETE OR INSERT OR UPDATE
ON account_transaction
 INSERTING
FOR EACH ROW
 DELETING
BEGIN
IF INSERTING
 UPDATING
THEN
:NEW.created_by := USER;
:NEW.created_date := SYSDATE;
ELSIF DELETING
THEN
audit_deletion(USER,SYSDATE);
12
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
END;
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;
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
13
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;
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
14
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;
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
15
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;
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
16
DDL trigger e.g.

Town crier examples triggered by creates:

uninformed_town_crier.sql

informed_town_crier.sql
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
17
Available DDL events


CREATE, ALTER, DROP, GRANT, RENAME,
REVOKE, TRUNCATE
DDL: any DDL event
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;

Q: Does this work??
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
18
DB Event triggers

Form similar to DDL triggers:
CREATE [OR REPLACE] TRIGGER trigger name
{BEFORE | AFTER} {database event} ON
{DATABASE | SCHEMA}
DECLARE
Variable declarations
BEGIN
... some code...
END;

Triggering events: STARTUP, SHUTDOWN,
SERVERERROR, LOGON, LOGOFF
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
19
DB event restrictions

Have BEFORE and AFTER as above, but they
don’t always apply:

No BEFORE STARTUP/LOGON/SERVERERROR

No AFTER SHUTDOWN/LOGOFF
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
20
DB Trigger e.g.

Gather stat before shutdown:
CREATE OR REPLACE TRIGGER on_shutdown
BEFORE SHUTDOWN ON DATABASE
BEGIN
gather_system_stats;
END;

Log error messages: error_log.sql
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
21
Trigger maintenance

Enabling & disabling:



Deleting:


DROP TRIGGER emp_after_insert;
Viewing:



ALTER TRIGGER emp_after_insert DISABLE;
ALTER TRIGGER emp_after_insert ENABLE;
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';
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
22
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
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
23
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
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
24
Simple trigger example

R(id, data, last-modified)




Goal: whenever data is modified, update lastmodified date
Could modify all scripts/programs that touch this
table
CREATE TRIGGER UpdateDateTrigger


data is a large string
Last-modified is a newly added date field
Bad idea
Better: user a trigger
BEFORE UPDATE OF data ON R
REFERENCING
NEW ROW AS NewTuple
FOR EACH STATEMENT
BEGIN
NewTuple.last-modified = sysdate;
END;
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
25
Triggers: Row-level example


MovieExec(name, address, cert#, netWorth)
“If someone decreases a movie executive’s 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.netWorth>NewTuple.netWorth)
UPDATE MovieExec
SET netWorth = oldTuple.netWorth
WHERE cert# = newTuple.cert#)
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
26
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 > (SELECT AVG(netWorth) FROM MovieExec))
BEGIN
DELETE FROM MovieExec
WHERE (Name, address, cert#, netWorth) IN NewStuff;
INSERT INTO MovieExec
(SELECT * FROM OldStuff);
END;
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
27
Mutating Table Errors

It’s generally impossible to a row-level trigger to
modify or query the table that triggered it


 trigger 2 slides back is not allowed!
Does not apply to statement-level triggers

 trigger 1 slide back is

Can do the equiv by creating a complicated
statement-level trigger

Won’t discuss…
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
28
Intermission

Go over previous labs

Begin lab…

Break
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
29
Transactions

So far, have simply issued commands


Recall, though: an xact is an operation/set of
ops executed atomically


Ignored xacts
In one instant
ACID test:


Xacts are atomic
Each xact (not each statement) must leave the DB
consistent
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
30
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…
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
31
Direct xact instructions

At any point, may explicitly COMMIT:




Conversely, can ROLLBACK



SQL> COMMIT;
Saves all statements entered up to now
Begins new xact
SQL> ROLLBACK;
Cancels all statements entered since start of xact
Example: delete from emp; or delete junk;
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
32
Direct xact instructions

Remember, DDL statements are autocommitted
 They cannot be rollbacked

Examples:

drop table junk;
rollback;
truncate table junk;
rollback;

Q: Why doesn’t rollback “work”?
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
33
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

Don’t have Word-like multiple undo


But do have savepoints
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
34
Savepoints





Create a savepoint:
SAVEPOINT savept_name;
--changes
SAVEPOINT sp1;
emp example:
--changes
Can skip savepoints SAVEPOINT sp2;
But can ROLLBACK --changes
SAVEPOINT sp3
only backwards
--changes
Can ROLLBACK
ROLLBACK TO sp2;
only to last COMMIT ROLLBACK TO sp1;
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
35
AUTOCOMMIT

Finally, can turn AUTOCOMMIT on:


SQL> SET AUTOCOMMIT ON;
Then each statement is auto-committed as its
own xact

Not just DDL statements
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
36
New topic: the bulk loader


To insert data, can insert rows one at a time
with INSERT INTO <table> VALUES(<>)
If data is in/can be computed from other
tables, can use



INSERT INTO <table> SELECT …
Often, have text file of data
Oracle’s bulk loader will parse file and insert
all into the database
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
37
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 <dataFile>
<APPEND> INTO TABLE <tableName>
FIELDS TERMINATED BY '<separator>'
(<list of all attribute names to load>)
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
38
The control file
LOAD DATA
INFILE <dataFile>
<APPEND> INTO TABLE <tableName>
FIELDS TERMINATED BY '<separator>'
(<list of all attribute names to load>)





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 doesn’t
matter
Separator can be multi-char
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
39
The control file

Example control file:
LOAD DATA
INFILE test.dat
INTO TABLE test
FIELDS TERMINATED BY '|'
(i, s)
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
40
The data file



Plain text file
Each line  one row in the table
Example data file:
1|foo
2|bar
3| baz
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
41
Running the bulk loader

The bulk loader is a command-line program
sqlldr, separate from SQL*Plus:
c:\ sqlldr scott/tiger control=test log=test bad=bad

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)
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
42
Data in the control file

Can also merge the data and control file
(onefile.ctl):
LOAD DATA
INFILE *
INTO TABLE test
FIELDS TERMINATED BY '|'
(i, s)
BEGINDATA
1|foo
2|bar
3| baz

The * indicates that the data is in this file
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
43
Loading dates


In the control file’s 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')
BEGINDATA
1|01-01-1990
2|4-1-1998
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
44
Loading timestamps

Similar to dates but includes more chars:
Mi – minutes
 ss – seconds
 hh – hour
 hh24: 24-hour hour
 ff – millisecond (fractional seconds)
LOAD DATA
INFILE *
withtimestamps.ctl:
APPEND INTO TABLE ts
FIELDS TERMINATED BY ','
(s, t timestamp 'yyyymmddhh24miss.ff' )
BEGINDATA
1,20041012081522.123
1,10661012081522.321
45
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005


Loading nulls




3||5
Two adjacent separators |2|4
are interpreted as a null 1||6
value in the field:
||7
What if null in last field?
Two options:
LOAD DATA
INFILE *
 Put TRAILING NULLCOLS
APPEND INTO TABLE nums
following field-term df
FIELDS TERMINATED BY '|'
 Append an extra field sep
TRAILING NULLCOLS (a,b,c)
to end
BEGINDATA
3||5
withnulls.ctl:
|2|4
1|2|
1|2||
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
46
Lecture 10…

regexps

Web apps/security

XML
Matthew P. Johnson, OCL2, CISDD CUNY, January 2005
47