OCL 4 Oracle 10 g SQL PLSQL Session

OCL 4 Oracle 10 g: SQL & PL/SQL Session #7 Matthew P. Johnson CISDD, CUNY June, 2005 Matthew P. Johnson, OCL 4, CISDD CUNY, Sept 2005 1

Agenda n n n Triggers Transactions Oracle’s bulk loader Go over some labs Do some more labs Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 2

New topic: Triggers n PL/SQL programs that run automatically (are “triggered”) when a certain event occurs n E. g. : on insert to some table On system start-up On delete from table n n Big benefit: need not be called explicitly However row in table x is deleted, the trigger gets called Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 3

Trigger events n n Trigger code may be “triggered” by many kinds of events: Oracle start-up/shut-down q n Data updates: q q n Triggers may replace initialization scripts Delete: maybe delete related rows Inserts Updates: maybe make other rows consistent Delete: maybe prevent DDL statements q Log creation of all objects, e. g. Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 4

Triggers n Constraints state what must remain true q DBMS decides when to check n Triggers are instructions to perform at explicitly specified times n Three aspects: q q q n 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, OCL 3, CISDD CUNY, June 2005 5

DML trigger options n The trigger may be: q q n The trigger may run q q q n 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 q q q INSERTs UPDATEs DELETEs Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 6

Trigger form CREATE [OR REPLACE] TRIGGER trigger name {BEFORE | AFTER | INSTEAD OF} {INSERT | DELETE | UPDATE OF column list} ON table name [FOR EACH ROW] [WHEN (. . . )] [DECLARE. . . ] BEGIN. . . executable statements. . . [EXCEPTION. . . ] END [trigger name]; Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 7

Trigger type examples n First run copy_tables. sql 1. statement_vs_row. sql q 2. before_vs_after. sql q 3. INSERT INTO to_table SELECT * FROM from_table; one_trigger_per_type. sql q INSERT INTO to_table VALUES (1); UPDATE to_table SET col 1 = 10; DELETE to_table; Matthew P. Johnson, OCL 3, CISDD CUNY, June 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; n Q: Why is this (maybe) better than client-side validation? Matthew P. Johnson, OCL 3, CISDD CUNY, June 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; n NB: WHEN applies only to row-level triggers Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 10

Triggers with WHEN n n Parentheses are required Can only call built-in functions in when q 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, 'HH 24') BETWEEN 9 AND 17 ). . . Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 11

Simple trigger example n R(id, data, last-modified) q q n n Goal: whenever data is modified, update lastmodified date Could modify all scripts/programs that touch this table CREATE TRIGGER Update. Date. Trigger q n data is a large string Last-modified is a newly added date field Bad idea Better: user a trigger AFTER UPDATE OF data ON R REFERENCING NEW ROW AS New. Tuple FOR EACH ROW BEGIN New. Tuple. last-modified = sysdate; END; Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 12

Multiple DML actions n DML actions may be ORed together CREATE OR REPLACE TRIGGER n To find actual three_for_the_price_of_one action, check: BEFORE DELETE OR INSERT OR UPDATE ON account_transaction q INSERTING FOR EACH ROW q DELETING BEGIN IF INSERTING q UPDATING THEN : NEW. created_by : = USER; : NEW. created_date : = SYSDATE; ELSIF DELETING THEN audit_deletion(USER, SYSDATE); 13 Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 END;

More on 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, OCL 3, CISDD CUNY, June 2005 14

Extended auditing example n n n 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, OCL 3, CISDD CUNY, June 2005 15

Extended auditing example n n Run: grades_tables. sql, grades_audit 2. 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, OCL 3, CISDD CUNY, June 2005 16

DDL Triggers n Respond to DDL events q q n 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, OCL 3, CISDD CUNY, June 2005 17

DDL trigger e. g. n Town crier examples triggered by creates: n uninformed_town_crier. sql n informed_town_crier. sql Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 18

Available DDL events n n 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; n Q: Does this work? ? Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 19

DB Event triggers n 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; n Triggering events: STARTUP, SHUTDOWN, SERVERERROR, LOGON, LOGOFF Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 20

DB event restrictions n Have BEFORE and AFTER as above, but they don’t always apply: q No BEFORE STARTUP/LOGON/SERVERERROR q No AFTER SHUTDOWN/LOGOFF Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 21

DB Trigger e. g. n Gather stats before shutdown: CREATE OR REPLACE TRIGGER on_shutdown BEFORE SHUTDOWN ON DATABASE BEGIN gather_system_stats; END; n Log error messages Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 22

Trigger maintenance n Enabling & disabling: q q n Deleting: q n DROP TRIGGER emp_after_insert; Viewing: q q n 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: q select object_name, status from user_objects where object_type='TRIGGER'; Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 23

Triggers – important points n n n 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 q q n n 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, OCL 3, CISDD CUNY, June 2005 24

Elements of Triggers n n n 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 q q once for every tuple or once for all the tuples that are changed by the database operation Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 25

Intermission n Go over previous labs n Begin lab… n Break Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 26

New-old topic: Transactions n So far, have simply issued commands q n Recall, though: an xact is an operation/set of ops executed atomically q n Ignored xacts In one instant ACID test: q q Xacts are atomic Each xact (not each statement) must leave the DB consistent Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 27

Default xact behavior n n An xact begins upon login By default, xact lasts until logoff q q n Except for DDL statements They automatically commit Examples with two views of emp… Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 28

Direct xact instructions n At any point, may explicitly COMMIT: q q q n Conversely, can ROLLBACK q q n 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, OCL 3, CISDD CUNY, June 2005 29

Direct xact instructions n Remember, DDL statements are autocommitted They cannot be rollbacked n Examples: n drop table junk; rollback; truncate table junk; rollback; n Q: Why doesn’t rollback “work”? Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 30

Savepoints n Xacts are atomic Can rollback to beginning of current xact n But might want to rollback only part way n n Make 10 changes, make one bad change Want to: roll back to before last change n Don’t have Word-like multiple undo n q But do have savepoints Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 31

Savepoints n n n Create a savepoint: SAVEPOINT savept_name; --changes SAVEPOINT sp 1; emp example: --changes Can skip savepoints SAVEPOINT sp 2; But can ROLLBACK --changes SAVEPOINT sp 3 only backwards --changes Can ROLLBACK TO sp 2; only to last COMMIT ROLLBACK TO sp 1; Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 32

AUTOCOMMIT n Finally, can turn AUTOCOMMIT on: q n SQL> SET AUTOCOMMIT ON; Then each statement is auto-committed as its own xact q Not just DDL statements Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 33

New topic: the bulk loader n n 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 q n n 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, OCL 3, CISDD CUNY, June 2005 34

Using the bulk loader n The bulk loader takes two files: q q n The data file The control file, specifying how to load the data Control file form: LOAD DATA INFILE <data. File> <APPEND> INTO TABLE <table. Name> FIELDS TERMINATED BY '<separator>' (<list of all attribute names to load>) Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 35

The control file LOAD DATA INFILE <data. File> <APPEND> INTO TABLE <table. Name> FIELDS TERMINATED BY '<separator>' (<list of all attribute names to load>) n n n 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, OCL 3, CISDD CUNY, June 2005 36

The control file n Example control file: LOAD DATA INFILE test. dat INTO TABLE test FIELDS TERMINATED BY '|' (i, s) Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 37

The data file n n n Plain text file Each line one row in the table Example data file: 1|foo 2|bar 3| baz Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 38

Running the bulk loader n The bulk loader is a command-line program sqlldr, separate from SQL*Plus: c: sqlldr scott/tiger control=test log=test bad=bad n At cmd line, specify: q q 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, OCL 3, CISDD CUNY, June 2005 39

Data in the control file n 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 n The * indicates that the data is in this file Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 40

Loading dates n n In the control file’s attribute list, follow a data field with a date mask Date masks are case-INsensitive and include: q q q n 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, OCL 3, CISDD CUNY, June 2005 41

Loading timestamps n Similar to dates but includes more chars: Mi – minutes q ss – seconds q hh – hour q hh 24: 24 -hour q ff – millisecond (fractional seconds) LOAD DATA INFILE * withtimestamps. ctl: APPEND INTO TABLE ts FIELDS TERMINATED BY ', ' (s, t timestamp 'yyyymmddhh 24 miss. ff' ) BEGINDATA 1, 20041012081522. 123 1, 10661012081522. 321 42 Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 q n

Loading nulls n n 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 * q Put TRAILING NULLCOLS APPEND INTO TABLE nums following field-term df FIELDS TERMINATED BY '|' q Append an extra field sep TRAILING NULLCOLS (a, b, c) to end BEGINDATA 3||5 withnulls. ctl: |2|4 1|2|| Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 43

Lecture 8… n regexps n Web apps/security n XML n Data warehousing extensions Matthew P. Johnson, OCL 3, CISDD CUNY, June 2005 44