c20 0046 database management systems lecture 20
Download
Skip this Video
Download Presentation
C20.0046: Database Management Systems Lecture #20

Loading in 2 Seconds...

play fullscreen
1 / 79

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


  • 109 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'C20.0046: Database Management Systems Lecture 20' - rasul


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.sqlhttp://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

ad