C20 0046 database management systems lecture 20
This presentation is the property of its rightful owner.
Sponsored Links
1 / 79

C20.0046: Database Management Systems Lecture #20 PowerPoint PPT Presentation


  • 75 Views
  • Uploaded on
  • Presentation posted in: General

C20.0046: Database Management Systems Lecture #20. M.P. Johnson Stern School of Business, NYU Spring, 2008. Agenda. Stored procedures? Triggers Transactions RAID? Implementation?. Programming: step back. Recall basic problem: need SQL plus stronger programming lang

Download Presentation

C20.0046: Database Management Systems Lecture #20

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


C20 0046 database management systems lecture 20

C20.0046: Database Management SystemsLecture #20

M.P. Johnson

Stern School of Business, NYU

Spring, 2008

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Agenda

Agenda

  • Stored procedures?

  • Triggers

  • Transactions

  • RAID?

  • Implementation?

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Programming step back

Programming: step back

  • Recall basic problem: need SQL plus stronger programming lang

    •  need to connect the two langs

  • In all these cases (and in the web app case), idea is: put SQL in (traditional-lang) programs

  • Another way: put programs in SQL

    • i.e., store programs on the DBMS

    • “stored procedures”

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Next topic sps

Next topic: SPs

  • “Persistent, Stored Modules” / “Stored Procedures / “PL/SQL programs” (in Oracle)

  • Another way to connect application programming language and SQL

  • Supports usual things:

    • Declare, set vars to vals of expressions

    • Print output

    • Define (optional) procedures, functions

    • Cursors

  • PL/SQL can compute n!

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Integration with sql

Integration with SQL

DECLARE

l_book_count INTEGER;

BEGIN

SELECT COUNT(*)

INTO l_book_count

FROM books

WHERE author LIKE '%FEUERSTEIN, STEVEN%';

DBMS_OUTPUT.PUT_LINE (

'Steven has written (or co-written) ' || l_book_count ||

' books.');

-- Oh, and I changed my name, so...

UPDATE books

SET author = REPLACE (author, 'STEVEN', 'STEPHEN')

WHERE author LIKE '%FEUERSTEIN, STEVEN%';

END;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Pl sql

PL/SQL

  • “Procedural Language/SQL”

    • Oracle’s language for stored procedures

    • MySQL’s is similar

  • Simple, interpreted, procedural language

  • But Pascal-like:

    • BEGIN END, not { }

    • AND OR, not && ||

    • vars defined at top of procedure

    • how return works

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Pl sql1

PL/SQL

  • Generally speaking can be used wherever SQL can be

    • Sql command-line

    • embeded SQL

    • JDBC/PHP/etc

  • Can store programs in files (.sql), run later

    • CALL myprog.sql runs code in myprog.sql

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Pl sql hello world

PL/SQL: Hello, World

  • http://pages.stern.nyu.edu/~mjohnson/oracle/plsql/hello.sql

BEGIN

-- print out message

DBMS_OUTPUT.PUT_LINE('Hello World, from PL/SQL');

END;

/

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Hello world

Hello, World

  • Try again…

SET SERVEROUTPUT ON

BEGIN

-- print out message

DBMS_OUTPUT.PUT_LINE('Hello World, from PL/SQL');

END;

/

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


How to run code

How to run code

  • The code before ended with a forward slash

  • Not SQL and not PL/SQL – just for SQL*Plus to tell it to run the code entered

  • Must go on its own line

    • O.w., will be ignored and then interpreted as part of code, causing an error

  • To call a procedure in SQL*Plus, can also use execute/exec:

exec DBMS_OUTPUT.PUT_LINE('Hello World, from PL/SQL')

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


How to run code1

How to run code

  • EXEC is just short-hand:

SQL> exec dbms_output.put_line('hi '); dbms_output.put_line('there'

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Sp operators symbols

SP operators/symbols

  • ;end statement

  • %attribute indicator (cursor attributes like %ISOPEN and indirect declaration attributes like %ROWTYPE

  • :host variable indicator

  • <> and !=not-equal-to

  • =equal-to

  • :=assignment op

  • ** exponentiation operator

  • -- , /* and */, rem comments

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Var names

Var names

  • identifiers limited to 30 alpha-num chars

    • Must start with letter, $, _, or #

  • E.g.: abc, $a$, $$$

  • PL/SQL is case INsensitive

    • abc, ABC, AbC all the same

    • Unless you use double-quotes…

  • Also supports constants:

    • Varname datatype CONSTANT := val;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Literals

Literals

  • Numbers: 123, 12.3, 3.05E19, 12e-5, null

  • String: 'abc', 'AbC', null

    • String comparison is case-SENSitive

  • Boolean: true, false, null

    • true != ‘true’

  • No date literals, as in regular SQL

    • To_date('31-JAN-94')

  • Escape single-quotes in strings with two single-quotes

  • 'it''s'it's

  • ''''''''

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Blocks

Blocks

  • SP languages are block-structured

  • Block = seq. of instructions, with scope

  • Can have anonymous blocks

  • And named blocks

    • Procedures

    • Functions

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Structure of a block

Structure of a block

header--if named

DECLARE--optional

--var declarations

BEGIN

--executable statements

--queries/updates, etc.

EXCEPTION--optional

--catch exceptions

END;

/--to execute

  • As in Pascal, var declars precede body

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Pl sql code examples

PL/SQL code examples

  • One example:

    • Likes(drinker, beverage)

  • Another example:

    • http://pages.stern.nyu.edu/~mjohnson/oracle/plsql/age.sql

BEGIN

INSERT INTO Likes VALUES(‘Izzy', ‘milk');

DELETE FROM Likes

WHERE drinker = ‘Izzy' AND

beverage = ‘Beaujolais Nouveau ';

COMMIT;

END; /

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Procedures

Procedures

  • Stored database objects that use a PL/SQL statement(s) in their body

  • Create/drop similar to other SQL objects:

CREATE PROCEDURE <my-proc>

(<params>) AS

<procedure body as above>;

CREATE OR REPLACE PROCEDURE <my-proc>(<params>) AS

<procedure body as above>;

DROP PROCEDURE <my-proc>;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Example procedure

Example procedure

  • Define the procedure:

  • Now we can call it:

CREATE PROCEDURE testProcedure AS

BEGIN

INSERT INTO Student VALUES (5, 'Joe');

END;

EXEC testProcedure

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


More details on procedures

More details on procedures

  • Parameter list has name-mode-type triples:

  • Modes: IN, OUT, or IN OUT

    • Fulfills role similar to pass-by-value v. pass-by-reference

    • Default is IN

  • Types must match, so can get exact field type:

relation.attribute%TYPE

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Procedure i o example

Procedure I/O example

  • A procedure to take a beer and price and add it to Joe's menu: Sells(bar, beer, price)

CREATE PROCEDURE izzyMenu(

b IN char(20),

p IN double) AS

BEGIN

INSERT INTO Sells

VALUES('Izzy''s', b, p);

END;

/

  • Are these the right types?

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Procedure i o example1

Procedure I/O example

  • A procedure to take a beer and price and add it to Joe's menu: Sells(bar, beer, price)

CREATE PROCEDURE izzyMenu(

b IN Sells.beer%TYPE,

p IN Sells.price%TYPE) AS

BEGIN

INSERT INTO Sells

VALUES('Izzy''s', b, p);

END;

/

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Larger procedure e g

Larger procedure e.g.

CREATE or replace PROCEDURE hike_prices(old_price number,

new_price out number,

percent_hike number := 5) is

Begin

new_price := old_price + old_price * percent_hike/100;

End;

/

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Call the procedure

Call the procedure

  • But how to use to modify table data?

  • Convert to a function

Declare

currprice number := 20;

newprice number;

Begin

hike_prices(currprice,newprice,5);

dbms_output.put_line(newprice);

End;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Functions

Functions

  • Like procedures but with return values

  • Big strength: can be called from SQL itself

CREATE FUNCTION <functionName>

(<paramList>) RETURN type AS

<localDeclarations>

BEGIN

<functionBody>

END;

DROP FUNCTION <functionName>;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Function example

Function example

  • http://pages.stern.nyu.edu/~mjohnson/oracle/plsql/maxval.sql

CREATE OR REPLACE FUNCTION maxval(a IN int, b IN int) RETURN int AS

BEGIN

IF a > b THEN RETURN a;

ELSE RETURN b;

END IF;

END maxval;

INSERT INTO R VALUES('abc', maxval(5,10));

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Hike function

Hike function

  • Now can use directly in update statements

  • NB: had to use different name for ftn

    • Same namespace for ftns & procs, although different

CREATE or replace FUNCTION hike_pricesf(old_price number,

percent_hike number := 5) return number is

Begin

return old_price + old_price * percent_hike/100;

End;

/

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


How to run scripts

How to run scripts

  • Usually don’t type ftns into sqlplus by hand

  • Define them in a .sql file

  • In sqlplus, execute .sql file

    • Runs commands in file

    • Here, defines function

  • Now, we can call functions (they persist)

  • See

    http://pages.stern.nyu.edu/~mjohnson/oracle/plsql/plsql.txt

SQL> CALL maxval.sql

SQL> exec DBMS_OUTPUT.PUT_LINE (maxval(5,10))

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Ed command

ED command

  • Though define procedures at cmd-prompt

  • Forgot AS!

  • Type ED…

SQL> create or replace procedure hello2

begin

dbms_output.put_line('hi');

end;

/

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Calling functions and procedures

Calling functions and procedures

  • Procedures can executed, ftns can’t

  • How to just call a ftn?

  • Can use dbms_output, as seen

  • Can also select the ftn value from dual

SQL> select(wordcount('hi there') from mytable;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Stored ftns procs persist

Stored ftns & procs persist

  • Once a function or procedure is created, it persists until it’s dropped

  • Stored procs are stored in the DB itself

    • In user_procedures in Oracle

  • Also, can describe ftns and procs:

CREATE OR REPLACE FUNCTION …

SELECT object_name from user_procedures;

SQL> describe wordcount

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Look up procedures functions

Look up procedures, functions

  • In Oracle, functions & procedures in user_procedures:

  • Also, can describe ftns and procs:

SELECT object_name from user_procedures;

SQL> describe wordcount

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Getting errors

Getting errors

  • Simply says:

  • To get actual errors, say SHOW ERR(ORS)

  • Can also get errors per object:

  • Warning: must get object type right!

  • Can also look at user_errors tbl directly

Warning: Function created with compilation errors.

SQL> show errors function wordcount

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Branching

Branching

  • IF–THEN statements use THEN

  • Must end with END IF

  • Use ELSIF in place of ELSE IF

  • Example:

    • http://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec19/maxval.sql

IF <condition> THEN

<statement(s)>

ELSIF

<statement(s)>

END IF;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Simple case statements

Simple case statements

CASE expression

WHEN result1 THEN

statements1

WHEN result2 THEN

statements2

...

ELSE

statements_else

END CASE;

  • General form:

  • ELSE is optional

  • expression and results are scalars

    • numbers, chars, strings, etc.; not tables

    • Literals or vars

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Simple case e g

Simple case e.g.

CASE employee_type

WHEN 'S' THEN

award_salary_bonus(employee_id);

WHEN 'H' THEN

award_hourly_bonus(employee_id);

WHEN 'C' THEN

award_commissioned_bonus(employee_id);

ELSE

RAISE invalid_employee_type;

END CASE;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Case statements in sql

CASE statements in SQL

  • By the way: CASE statements are now supported in Oracle SQL itself

SELECT

CASE

WHEN comm is null THEN 0

ELSE comm

END

FROM emp;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Loop example

Loop example

DECLARE    

i NUMBER := 1;

BEGIN    

LOOP        

INSERT INTO T1VALUES(i,i);

i := i+1;        

EXIT WHEN i>100;    

END LOOP;

END;

/

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


More loops

More loops

  • Numerical for loop:

  • Cursor for loop:

FOR for_index IN low_value .. high_value

LOOP

executable_statements;

END LOOP;

FOR record_index IN my_cursor

LOOP

executable_statements;

END LOOP;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Word count program

http://pages.stern.nyu.edu/~mjohnson/oracle/plsql/wordcount.sql

CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)

RETURN PLS_INTEGER AS

/* words PLS_INTEGER := 0; ***Commented out for intentional error*** */

len PLS_INTEGER := NVL(LENGTH(str),0);

inside_a_word BOOLEAN;

BEGIN

FOR i IN 1..len + 1 LOOP

IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len

THEN

IF inside_a_word

THEN

words := words + 1;

inside_a_word := FALSE;

END IF;

ELSE

inside_a_word := TRUE;

END IF;

END LOOP;

RETURN words;

END;

Word count program

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Explicit cursors v for loop cursors

Explicit cursors v. for loop cursors

DECLARE

CURSOR occupancy_cur IS

SELECT pet_id, room_number

FROM occupancy WHERE occupied_dt = TRUNC (SYSDATE);

occupancy_rec occupancy_cur%ROWTYPE;

BEGIN

OPEN occupancy_cur;

LOOP

FETCH occupancy_cur INTO occupancy_rec;

EXIT WHEN occupancy_cur%NOTFOUND;

update_bill

(occupancy_rec.pet_id, occupancy_rec.room_number);

END LOOP;

CLOSE occupancy_cur;

END;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Explicit cursors v for loop cursors1

Explicit cursors v. for loop cursors

DECLARE

CURSOR occupancy_cur IS

SELECT pet_id, room_number

FROM occupancy WHERE occupied_dt = TRUNC (SYSDATE);

BEGIN

FOR occupancy_rec IN occupancy_cur

LOOP

update_bill (occupancy_rec.pet_id, occupancy_rec.room_number);

END LOOP;

END;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Using query results select into

Using query results: SELECT … INTO

create or replace function getprod(manuf varchar) return varchar as

pn varchar(255);

begin

select prodname into pn

from products

where mfg = manuf;

return pn;

end;

/

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Select into and exceptions

SELECT … INTO and exceptions

create or replace function getprod(manuf varchar) return varchar as

pn varchar(255);

begin

select prodname into pn

from products

where mfg = manuf;

return pn;

Exception

When TOO_MANY_ROWS then

dbms_output.put_line('got too many');

end;

/

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Programs and rights

Programs and rights

  • By default, only the creator of a program may run it (apart from the admin)

  • If others should run, must GRANT them permission:

  • Permissions can be revoked:

  • Can also grant to particular roles or everyone:

  • Wider/narrower grant ops are independent…

SQL> GRANT EXECUTE ON wordcount TO george;

SQL> REVOKE EXECUTE FROM wordcount TO george;

SQL> GRANT EXECUTE ON wordcount TO dba_role;

SQL> GRANT EXECUTE ON wordcount TO public;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Packages

Packages

  • Functions and procedures (and vars) can be grouped in packages

    • Like Java packages, C++ namespaces, etc.

  • A pkg has a specification and a body

    • Somewhat like C++ class definitions

  • Specification: declares public functions

    • “public” means: can be run by a user with EXECUTE authority on this pkg

  • Body: defines all functions

    • Vars defined here are visible to the pkg’s programs

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Dynamic pl sql

Dynamic PL/SQL

  • Saw “dynamic SQL” in the cases of Pro*C and JDBC

    • Ability to run ad-hoc (non-hard-coded) SQL in programs/scripts

  • Can also do this in PL/SQL

  • The string can be passed in, created from concatenation, etc.

EXECUTE IMMEDIATE <string>;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Dynamic pl sql1

Dynamic PL/SQL

  • E.g.: write function to return number rows in an arbitrary table

CREATE OR REPLACE FUNCTION rowCount (

tabname IN VARCHAR2) return integer as

retval integer;

begin

execute immediate 'select count(*) from ' || tabname into retval;

return retval;

end;

/

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Dynamic pl sql for ddl

Dynamic PL/SQL for DDL

  • Ordinarily can’t do DDL in PL/SQL

  • But you can in dynamic PL/SQL

  • Here’s an e.g.:

CREATE OR REPLACE procedure dropproc(procname in varchar2) as

begin

execute immediate 'drop procedure ' || procname;

end;

/

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Live examples

Live examples

  • Factorial function:

    • http://pages.stern.nyu.edu/~mjohnson/dbms/plsql/fact.sql

  • Converting between bases:

    • http://pages.stern.nyu.edu/~mjohnson/dbms/plsql/numsys.sql

  • Directory of examples:

    • http://pages.stern.nyu.edu/~mjohnson/dbms/plsql/

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Sps in mysql 5 0

SPs in MySQL (5.0)

  • Generally similar, though technical diffs

    • Need to temporarily redefine ; delimiter

mysql> drop procedure if exists hello;

myslq> delimiter /

mysql> create procedure hello()

-> begin

-> select 'hi';

-> end;

-> /

mysql> delimiter ;

mysql> call hello();

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


New topic triggers

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

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Trigger events

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.

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Triggers

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 trigger’s effect) (deletion, update, insertion)

  • When the event occurs, DBMS checks the constraint, and if it is satisfied, performs the action

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Dml trigger options

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

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Trigger form

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];

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Trigger type examples

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;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Dml trigger e g

DML Trigger e.g.

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

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;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Triggers with when

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

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Triggers with when1

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

BEFORE INSERT ON frame

FOR EACH ROW

WHEN ( TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 )

...

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Simple trigger example

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;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Multiple dml actions

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

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


More on updating

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;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Extended auditing example

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;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Extended auditing example1

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;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Ddl triggers

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;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Ddl trigger e g

DDL trigger e.g.

  • Town crier examples triggered by creates:

  • uninformed_town_crier.sql

  • informed_town_crier.sql

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Available ddl events

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;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Db event triggers

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;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Db event restrictions

DB event restrictions

  • Have BEFORE and AFTER as above, but they don’t always apply:

    • No BEFORESTARTUP/LOGON/SERVERERROR

    • No AFTERSHUTDOWN/LOGOFF

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Db trigger e g

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;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


New old topic transactions

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

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Default xact behavior in oracle

Default xact behavior (in Oracle)

  • An xact begins upon login

  • By default, xact lasts until logoff

    • Except for DDL statements

    • They automatically commit

  • Examples with two views of tbl…

    • But with TYPE=innodb !

    • mysql> set autocommit = 0

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Direct xact instructions

Direct xact instructions

  • At any point, may explicitly COMMIT:

    • SQL> COMMIT;

    • Saves all statements entered up to now

    • Begins new xact

  • Conversely, can ROLLBACK

    • SQL> ROLLBACK;

    • Cancels all statements entered since start of xact

  • Example: delete from emp; or delete junk;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Direct xact instructions1

Direct xact instructions

  • Remember, DDL statements are auto-committed

  •  They cannot be rollbacked

  • Examples:

  • Q: Why doesn’t rollback “work”?

drop table junk;

rollback;

truncate table junk;

rollback;

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Savepoints

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

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Savepoints1

Savepoints

  • Create a savepoint:

  • emp example:

SAVEPOINT savept_name;

--changes

SAVEPOINT sp1;

--changes

SAVEPOINT sp2;

--changes

SAVEPOINT sp3

--changes

ROLLBACK TO SAVEPOINT sp2;

ROLLBACK TO SAVEPOINT sp1;

  • Can skip savepoints

  • But can ROLLBACK only backwards

  • Can ROLLBACK only to last COMMIT

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Autocommit

AUTOCOMMIT

  • Finally, can turn AUTOCOMMIT on:

    • Oralce: SQL> SET AUTOCOMMIT ON;

    • Mysql: mysql> SET AUTOCOMMIT=1;

    • Can put this in your config file

    • Can specify through JDBC, etc.

  • Then each statement is auto-committed as its own xact

    • Not just DDL statements

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


Raid levels

RAID levels

  • RAID level 1: each disk gets a mirror

  • RAID level 4: one disk is xor of all others

    • Each bit is sum mod 2 of corresponding bits

  • E.g.:

    • Disk 1: 10110011

    • Disk 2: 10101010

    • Disk 3: 00111000

    • Disk 4:

  • How to recover?

  • What’s the disadvantage of R4?

    • Various other RAID levels in text…

M.P. Johnson, DBMS, Stern/NYU, Spring 2008


  • Login