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

1. CPSC 4670 Database Security and AuditingOracle PL/SQL: Triggers

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

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

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

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

6. Trigger example • trig2.sql • trig2test.sql • SQL> start trig2test Then double check the restock table and part table for the results of trig2.sql

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

8. 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))

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

10. Database Access Using Cursors

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

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

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

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

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

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

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

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

19. Cursor Example • Use of the cursor statements and attributes • p7.sql

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

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

22. Cursor for loop example declare cursor c1 is select cno, cname, city from customers, zipcodes where customers.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

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

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

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

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

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

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

29. 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’)

30. Records and Tables

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

32. 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; /

33. 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; /

34. 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; /

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

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

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

38. Built-In Packages • dbms_output for debugging purpose • dbms_sql is used for executing dynamic SQ and PL/SQL statements

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

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

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

42. dbms_sql Package • Execute nonquery statements • Open the cursor • Parse the statement • Bind any input variables • Execute the statement • Close the cursor • p18.sql

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

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

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

46. 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'

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

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