Cpsc 4670 database security and auditing oracle pl sql triggers
1 / 48

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

  • Uploaded on

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 .

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

PowerPoint Slideshow about ' CPSC 4670 Database Security and Auditing Oracle PL/SQL: Triggers' - zudora

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


  • 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.


create [or replace] trigger trigger-name

{before | after}

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


{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)] ]



  • 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(

    pno number(5),

    username char(8),

    update_date date,

    old_price number(6,2),

    new_price number(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

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


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:


    cursor c is select * from sailors;

    sailorData sailors%ROWTYPE;


    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



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

area NUMBER(14,2);

cursorrad_cursor isselect * from RAD_VALS;

rad_val rad_cursor%ROWTYPE;


open rad_cursor;

fetch rad_cursorinto rad_val;


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

close rad_cursor;












cursor rad_cursor is select * from RAD_VALS;



open rad_cursor;

fetch rad_cursor into rad_val;


insert into AREAS values (rad_val, area);


cursor rad_cursor is select * from RAD_VALS;



open rad_cursor;

fetch rad_cursor into rad_val;


insert into AREAS values (rad_val.radius,





cursor rad_cursor is select * from RAD_VALS;



open rad_cursor;

fetch rad_cursor into rad_val;


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


cursor rad_cursor is selectradiusfrom




open rad_cursor;

fetch rad_cursor into rad_val;


insert into AREAS values (rad_val,area); …



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


    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


cursor c1 is

select cno, cname, city

from customers, zipcodes

where customers.zip = zipcodes.zip;


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



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


  • 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’)


  • 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


customer_rec customers%rowtype;


select *

into customer_rec

from customers

where cno = '4444';

if (customer_rec.phone is null) then

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


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

end if;



Cursor based records
Cursor-based records


cursor c1 is

select orders.eno employee_no,


sum(price*qty) total_sales

from employees,orders,odetails,parts

where employees.eno = orders.eno


orders.ono = odetails.ono and

odetails.pno = parts.pno

group by orders.eno, ename;

emp_sales_rec c1%rowtype;


open c1;


fetch c1 into emp_sales_rec;

exit when c1%NOTFOUND;


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

end loop;

close c1;



Programmer defined records
Programmer defined records


TYPEmy_rec_typeIS RECORD

(number integer,

name varchar2(20));

r1 my_rec_type;

r2 my_rec_type;


r1.number := 111;

r1.name := 'jones';

r2 := r1;

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

' Name = ' || r2.name);



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.


    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


  • 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);



  • 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


  • 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