Cpsc 4670 database security and auditing oracle pl sql triggers
This presentation is the property of its rightful owner.
Sponsored Links
1 / 48

CPSC 4670 Database Security and Auditing Oracle PL/SQL: Triggers PowerPoint PPT Presentation


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

CPSC 4670 Database Security and Auditing Oracle PL/SQL: Triggers. Triggers. An SQL trigger is a mechanism that automatically executes a specified PL/SQL block when a triggering event occurs on a table. The triggering event may be one of insert , delete , or update .

Download Presentation

CPSC 4670 Database Security and Auditing Oracle PL/SQL: Triggers

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


Cpsc 4670 database security and auditing oracle pl sql triggers

CPSC 4670 Database Security and AuditingOracle PL/SQL: Triggers


Triggers

Triggers

  • AnSQL trigger is a mechanism that automatically executes a specified PL/SQL block when a triggering event occurs on a table.

  • The triggering event may be one ofinsert, delete, or update.

  • The trigger is associated with a database table and is fired when the triggering event takes place on the table.


Triggers1

Triggers

create [or replace] trigger trigger-name

{before | after}

{delete | insert | update [of column [, column] …]}

[or

{delete | insert | update [of column [, column …]}

]…

ON table-name

[ [referencing {old [as] <old> [new [as] <new>]

| new [as] <new> [old [as] <old> }]

for each row

[when (condition)] ]

pl/sql_block


Triggers2

Triggers

  • referencing specifies correlation names that can be used to refer to the old and new values of the row components that are being affected by the trigger

  • for each row designates the trigger to be a row trigger, i.e., the trigger is fired once for each row that is affected by the triggering event and meets the optional trigger constraint defined in the when clause.

  • when specifies the trigger restriction.


Trigger example

Trigger example

  • A trigger is executed when a row is inserted into the odetails table.

  • The trigger checks to see if the quantity ordered is more than the quantity on hand. If it is, an error message is generated, and the row is not inserted.

  • Otherwise, the trigger updatesthe quantity on hand for the part and checks to see if it has fallen below the reorder level. If it has, it sends a row to the restock table indicating that the part need to be reordered.


Trigger example1

Trigger example

  • trig2.sql

  • trig2test.sql

  • SQL> start trig2test

    Then double check the restock table and part table for the results of trig2.sql


Trigger example 2

Trigger example (2)

  • The trigger is defined on the parts table and is triggered when the price column is updated. Each time someone updates the price of a particular part, the trigger makes an entry in a log file of this update along with the userid of the person performing the update and the date of the update.


Trigger example 21

Trigger example (2)

  • The log file is created using

    Create table parts_log(

    pnonumber(5),

    usernamechar(8),

    update_datedate,

    old_pricenumber(6,2),

    new_pricenumber(6,2))


Trigger is defined as

Trigger is defined as

  • trig3.sql

  • SQL>update parts set price = 55.00 where pno = 1099;

  • SQL> select * from parts_log;

  • Use trig_tables.sql to test trig3.sql


Cpsc 4670 database security and auditing oracle pl sql triggers

Database Access Using Cursors


Database access using cursors

Database Access Using Cursors

  • When the result of an SQL query (select statement) consists of more than one row, the simple select into statement can not be used.

  • A PL/SQL cursor allows the program to fetch and process information from the database into the PL/SQL program, one row at a time.


Explicit cursor

Explicit Cursor

  • Explicit cursor: used for processing a query resulting in more than one row.

  • Implicit cursor: is automatically defined by PL/SQL for the select into statements, which result in one or fewer rows.

cursor <cname> [return-spec] is

<select-statement>;


Cursor example

Cursor Example

cursor c1 return customers%rowtypeis

select * from customers;

cursor c2 is

select pno, pname, price*markdown sale_price

from parts

has return clause

Use PL/SQL variable markdown


Process cursor

Process cursor

  • One a cursor has been declared, it can be processed using the open, fetch, and close statements.

    open <cname>;

    fetch <cname> into <Record-or-VariableList>;

    close <cname>;


Explicit cursor attributes

Explicit Cursor Attributes

Obtain status information about a cursor.

Attribute Type Description

%ISOPEN Boolean Evaluates to TRUE if the cursor is open.

%NOTFOUND Boolean Evaluates to TRUE if the most recent fetch does not return a row.

%FOUND Boolean Evaluates to TRUE if the most recent fetch returns a row; complement of %NOTFOUND

%ROWCOUNT Number Evaluates to the total number of rows returned so far.


Creating a cursor

Creating a Cursor

  • We create a Cursor when we want to go over a result of a query (like ResultSet in JDBC)

  • Syntax Example:

    DECLARE

    cursor c is select * from sailors;

    sailorData sailors%ROWTYPE;

    BEGIN

    open c;

    fetch c into sailorData;

sailorData is a variable that can hold a ROW from the sailors table

Here the first row of sailors is inserted into sailorData


Cursor example1

Cursor Example

RAD_VALS

DECLARE

Pi constant NUMBER(8,7) := 3.1415926;

area NUMBER(14,2);

cursorrad_cursor isselect * from RAD_VALS;

rad_val rad_cursor%ROWTYPE;

BEGIN

open rad_cursor;

fetch rad_cursorinto rad_val;

area:=pi*power(rad_val.radius,2);

insert into AREAS values (rad_val.radius, area);

close rad_cursor;

END;

/

Rad_cursor

fetch

Rad_val

AREAS

Radius

Area

3

28.27


Cpsc 4670 database security and auditing oracle pl sql triggers

DECLARE

cursor rad_cursor is select * from RAD_VALS;

rad_valRAD_VALS.radius%TYPE;

BEGIN

open rad_cursor;

fetch rad_cursor into rad_val;

area:=pi*power(rad_val,2);

insert into AREAS values (rad_val, area);

DECLARE

cursor rad_cursor is select * from RAD_VALS;

rad_valrad_cursor%ROWTYPE;

BEGIN

open rad_cursor;

fetch rad_cursor into rad_val;

area:=pi*power(rad_val.radius,2);

insert into AREAS values (rad_val.radius,

area);

1

4

DECLARE

cursor rad_cursor is select * from RAD_VALS;

rad_valRAD_VALS%ROWTYPE;

BEGIN

open rad_cursor;

fetch rad_cursor into rad_val;

area:=pi*power(rad_val.radius,2);

insert into AREAS values (rad_val.radius, area);

DECLARE

cursor rad_cursor is selectradiusfrom

RAD_VALS;

rad_valRAD_VALS.radius%TYPE;

BEGIN

open rad_cursor;

fetch rad_cursor into rad_val;

area:=pi*power(rad_val,2);

insert into AREAS values (rad_val,area); …

2

3


Cursor example2

Cursor Example

  • Use of the cursor statements and attributes

  • p7.sql


Cursor for loop

Cursor for loop

  • This loop is very useful when all rows of the cursors are to be processed.

    for <record_index> in <cname> loop

    <loop-body>;

    end loop;

  • <record_index> is a record variable that is implicitly declared by PL/SQL. Its scope is the for loop, and it can not be accessed outside the for loop.


Cursor for loop1

Cursor for loop

  • The loop terminates automatically when all rows of the cursor have been fetched.

  • There is no need to open, fetch, or close the curse, and there is no need to declare the record into which the cursor rows are to be fetched.


Cursor for loop example

Cursor for loop example

declare

cursor c1 is

select cno, cname, city

fromcustomers, zipcodes

wherecustomers.zip = zipcodes.zip;

begin

for c1_rec in c1 loop

dbms_output.put-line(‘Row number ’ || c1%rowcount || ‘> ‘ || c_rec.cno || ‘ ‘ || c1-rec.cname || ‘ ‘ || c1_rec.city);

end loop

end;

c1_rec

No declare for the record into which the cursor rows are to be fetched


Parameterized cursors

Parameterized Cursors

  • PL/SQL allows for cursors to take input parameters.

    cursor <cname> (<parameter-list>)

    [return <return-spec>]

    is <select-statement>

  • The parameters are specified immediately after the cursor name


Parameterized cursors1

Parameterized Cursors

Cursor c3(city_in zipcodes.city%type) is

select orders.eno, ename, sum(qty*price) Sale

from employees, orders, odetails, parts, zipcodes

where employees.eno = orders.eno and

orders.ono = odetails.ono and

odetails.pno = parts.pno and

employees.zip = zipcodes.zip and

zipcodes.city = city_in

Group by order.eno, ename

Given a city, this cursor returns the sales totals for every employee from that city


Parameterized cursors2

Parameterized Cursors

  • The open statement for such cursors will have the actual augment.

    open ci3(‘Wichita’);

  • If a cursor loop is used to process this cursor

    for c3_rec in c3(‘Wichita’) loop

    end loop;


Select for update

select for update

  • PL/SQL cursors can also be used to perform updates

    cursor <cname> is

    <select-statement> for update;

  • Select statement should involve only one database table

    update <table-name>

    set<set-clause>

    where current of <cname>;

    delete from < table-name >

    where current of <cname>;


Example

Example

  • The price of every part whose quantity-on-hand value is more than 175 is set to 80% of its old price.

  • p8.sql


Cursor variables

Cursor variables

  • Cursor variables are not required to have the SQL select statement associated with them at the time of their declaration.

  • Different SQL select statements can be associated with cursor variables at different times.

    type <cursor-var-type-name> is ref cursor

    [return <return-type>];


Cursor variables example

Cursor variables example

  • Display the table whose name is an input parameter

  • A cursor variable is declared, with its return type not state in the declaration.

  • p9.sql

  • SQL> execute display_table(‘customers’)

  • SQL> execute display_table(‘abcd’)


Records and tables

Records and Tables


Records

Records

  • Table-based records, whose structure is the same as that of a row in a database table. p10.sql

  • Cursor-based records is based on the select list of a cursor. p11.sql

  • Programmer defined records. A type declaration is needed before record variables can be declared. p12.sql


Table based records

Table-based records

DECLARE

customer_rec customers%rowtype;

BEGIN

select *

into customer_rec

from customers

where cno = '4444';

if (customer_rec.phone is null) then

dbms_output.put_line('Phone number is absent');

else

dbms_output.put_line('Phone number is ' || customer_rec.phone);

end if;

END;

/


Cursor based records

Cursor-based records

DECLARE

cursor c1 is

select orders.eno employee_no,

enameemployee_name,

sum(price*qty) total_sales

from employees,orders,odetails,parts

where employees.eno = orders.eno

and

orders.ono = odetails.ono and

odetails.pno = parts.pno

group by orders.eno, ename;

emp_sales_rec c1%rowtype;

BEGIN

open c1;

loop

fetch c1 into emp_sales_rec;

exit when c1%NOTFOUND;

dbms_output.put_line(

emp_sales_rec.employee_no || ' ' || emp_sales_rec.employee_name || ' ' || emp_sales_rec.total_sales);

end loop;

close c1;

END;

/


Programmer defined records

Programmer defined records

DECLARE

TYPEmy_rec_typeIS RECORD

(number integer,

name varchar2(20));

r1 my_rec_type;

r2 my_rec_type;

BEGIN

r1.number := 111;

r1.name := 'jones';

r2 := r1;

dbms_output.put_line('Number = ' || r2.number ||

' Name = ' || r2.name);

END;

/


Pl sql tables

PL/SQL Tables

  • PL/SQL tables always consist of just one column indexed by binary integer

  • They are sparse and provide direct access to these rows, much like a hash table.

    type <table-type-name> is table of <datatype>

    Index by binary_integer


Operations of tables

Operations of tables

  • count returns the number of elements in the table. n := the_table.count;

  • delete deletes the specified row or all rows. the_table.delete(43);

  • exists returns true if there exists a row in the specified index, otherwise, it returns false. If the_table.exists(3) then …

  • first returns the lowest-valued index

  • last returns the highest-valued index

  • next returns the next-higher-valued index

  • prior returns the next-lower-valued index


Table example

Table example

  • Retrieves information from the Oracle data dictionary table user_tab_columns and prints the relational schemas of tables whose names start with the letter “Z”

  • p13.sql

  • SQL> start p10


Built in packages

Built-In Packages

  • dbms_output for debugging purpose

  • dbms_sql is used for executing dynamic SQ and PL/SQL statements


Dbms output package

dbms_output package

  • dbms_output package allows the user to display information to the session’s output device as the PL/SQL program executes.

  • disable all calls to the dbms_output package.

    dbms_output.disable

    SQL> set serverouput off


Dbms output package1

dbms_output package

  • enable enables all calls to the dbms_output package.

    dbms_output.enable(1000000) will initialize the buffer size to 1000000.

    Or dbms_output.enable;

    SQL> set serveroutput on size 1000000

    SQL> set serveroutput on


Dbms output package2

dbms_output package

  • new_line inserts an en-of-line marker in the buffer.

  • put puts information into the buffer.

  • put-line is the same as put, except that an end-of-line marker is also placed in the buffer.

  • get_line retrieves one line of information from the buffer.

  • get_lines retrieves a specified number of lines from the buffer.


Dbms sql package

dbms_sql Package

  • Execute nonquery statements

    • Open the cursor

    • Parse the statement

    • Bind any input variables

    • Execute the statement

    • Close the cursor

  • p18.sql


Dbms sql package1

dbms_sql Package

  • Executing Queries

    • Open the cursor

    • Parse the statement

    • Bind any input variables

    • Define the output variables

    • Execute the query

    • Fetch the rows

    • Return the results to PL/SQL variables

    • Close the cursor


Dbms sql package2

dbms_sql Package

  • p20.ql creates a package dsql with two procedures get_columns and get_query_results.

  • p21.sql creates a package dsql_driver with two procedures and uses the types and procedures get_columns and get_query_results in p20.sql.

  • p22.sql is an anonymous PL/SQL block that calls procedures drive_get_query_results from package dsql_driver in p21.sql


Error handling

Error Handling

  • PL/SQL implements run-time error handling via exceptions and exception handlers.

    when <exception-name> then

    <Error-Handling-Code>

  • User defined exception

    <exception-name> exception; and are raised using the syntax

    raise <exception-name>


Exception example

Exception example

  • p40.sql

  • SQL> execute insert_odetails(1234, 1111, -5);

    • 'Quantity is invalid'

  • SQL> execute insert_odetails(2000, 10900, 10);

  • SQL> execute insert_odetails(2000, 11001, 10);

    • 'PRIMARY KEY VIOLATION'


Exercises

Exercises

  • Try all the following triggers:

    • trig1.sql; trig2.sql; trig2test.sql; trig3.sql; trig_tables.sql (SQL script for creating tables for the triggers).

  • Try all the following cursors:

    • p7.sql; p8.sql (Updatable Cursor); p9.sql (Cursor Variable)

  • Try all the following tables:

    • p10.sql (Table-based record); p11.sql (Cursor-based record); p12.sql (Programmer-defined record)

  • Try the table: p13.sql


Exercises1

Exercises

  • Try all the packages

    • p18.sql (DBMS_SQL Package -- Executing non-query SQL)

    • p19.sql (DBMS_SQL Package -- Executing drop/create)

    • p20.sql; p21.sql; p22.sql (DBMS_SQL - Executing Query)

  • Try the Exception Handling: p40.sql


  • Login