Chapter 13 PROCEDURES FUNCTIONS PACKAGES and TRIGGERS

Chapter 13 : PROCEDURES, FUNCTIONS, PACKAGES, and TRIGGERS Bordoloi and Bock

Learning Objectives • Create and drop procedures – includes passing parameters and values. • Create and drop functions – includes returning values. • Create package specifications, package bodies, stored, packages, cursor processing in packages, and calling stored packages. • Create, alter, drop, enable, and disable triggers – includes before and after triggers. Bordoloi and Bock

Procedures and Functions • Oracle subprograms – includes both procedures and functions. • Both procedures and functions: – Can be programmed to perform a data processing task. – Are named PL/SQL blocks, and both can be coded to take parameters to generalize the code. – Can be written with declarative, executable, and exception sections. • Functions are typically coded to perform some type of calculation. • Primary difference – procedures are called with PL/SQL statements while functions are called as part of an expression. Bordoloi and Bock

Procedures and Functions • Procedures and functions: – Normally stored in the database within package specifications – a package is a sort of wrapper for a group of named blocks. – Can be stored as individual database objects. – Are parsed and compiled at the time they are stored. – Compiled objects execute faster than nonprocedural SQL scripts because nonprocedural scripts require extra time for compilation. – Can be invoked from most Oracle tools like SQL*Plus, and from other programming languages like C++ and JAVA. Bordoloi and Bock

Benefits of Subprograms • Improved data security – controls access to database objects while enabling non-privileged application users to access just the data needed. • Improved data integrity – related actions on database tables are performed as a unit enforcing transaction integrity – all updates are executed or none are executed. • Improved application performance – avoids reparsing objects used by multiple users through the use of shared SQL for Oracle – reduces number of database calls thus reducing network traffic. • Improved maintenance – procedures and functions that perform common tasks can be modified without having to directly work on multiple applications that may call these common procedures and functions – this approach eliminates duplicate testing. Bordoloi and Bock

Procedures • Procedures are named PL/SQL blocks. • Created/owned by a particular schema • Privilege to execute a specific procedure can be granted to or revoked from application users in order to control data access. • Requires CREATE PROCEDURE (to create in your schema) or CREATE ANY PROCEDURE privilege (to create in other schemas). Bordoloi and Bock

CREATE PROCEDURE Syntax CREATE [OR REPLACE] PROCEDURE ( , , . . . ) {AS|IS} BEGIN Executable statements [EXCEPTION Exception handlers] END ; • • • Unique procedure name is required. OR REPLACE clause facilitates testing. Parameters are optional – enclosed in parentheses when used. AS or IS keyword is used – both work identically. Procedure variables are declared prior to the BEGIN keyword. DECLARE keyword is NOT used in named procedure. Bordoloi and Bock

Compiling and Showing Errors • To Compile/Load a procedure use either the “@” symbol or the START SQL command to compile the file. The parameter is the. sql file that contains the procedure to be compiled. SQL>@ SQL>start • Filename does not need to be the same as the procedure name. The. sql file only contains the procedure code. • Compiled procedure is stored in the database, not the. sql file. • Use SHOW ERRORS command if the procedure does not compile without errors. Use EXECUTE to run procedure. SQL> show errors; SQL> EXECUTE Insert_Employee Bordoloi and Bock

Parameters • Both procedures and functions can take parameters. • Values passed as parameters to a procedure as arguments in a calling statement are termed actual parameters. • The parameters in a procedure declaration are called formal parameters. • The values stored in actual parameters are values passed to the formal parameters – the formal parameters are like placeholders to store the incoming values. • When a procedure completes, the actual parameters are assigned the values of the formal parameters. • A formal parameter can have one of three possible modes: (1) IN, (2), OUT, or (3) IN OUT. Bordoloi and Bock

Defining the IN, OUT, and IN OUT Parameter Modes • IN – this parameter type is passed to a procedure as a read-only value that cannot be changed within the procedure – this is the default mode. • OUT – this parameter type is write-only, and can only appear on the left side of an assignment statement in the procedure – it is assigned an initial value of NULL. • IN OUT – this parameter type combines both IN and OUT; a parameter of this mode is passed to a procedure, and its value can be changed within the procedure. • If a procedure raises an exception, the formal parameter values are not copied back to their corresponding actual parameters. Bordoloi and Bock

Parameter Constraint Restrictions • Procedures do not allow specifying a constraint on the parameter data type. • Example: the following CREATE PROCEDURE statement is not allowed because of the specification that constrains the v_Variable parameter to NUMBER(2). Instead use the general data type of NUMBER. /* Invalid constraint on parameter. */ CREATE OR REPLACE PROCEDURE pro. Sample (v_Variable NUMBER(2), . . . ) /* Valid parameter. */ CREATE OR REPLACE PROCEDURE pro. Sample (v_Variable NUMBER, . . . ) Bordoloi and Bock

Example 13. 1 (1 of 2) /* PL SQL Example 13. 1 File: ch 13 -1. sql */ CREATE OR REPLACE PROCEDURE Update. Equipment ( p_Equipment. Number IN Equipment. Number%TYPE, p_Description IN Equipment. Description%TYPE, p_Cost IN Equipment. Original. Cost%TYPE, p_Quantity IN Equipment. Quantity. Available%TYPE, p_Project IN Equipment. Project. Number%TYPE ) AS e_Equipment. Not. Found EXCEPTION; v_Error. TEXT VARCHAR 2(512); Bordoloi and Bock

Example 13. 1 (2 of 2) BEGIN UPDATE Equipment SET Description = p_Description, Original. Cost = p_Cost, Quantity. Available = p_Quantity, Project. Number = p_Project WHERE Equipment. Number = p_Equipment. Number; IF SQL%ROWCOUNT = 0 THEN Raise e_Equipment. Not. Found; END IF; EXCEPTION WHEN e_Equipment. Not. Found THEN DBMS_OUTPUT. PUT_LINE ('Invalid Equipment Number: ' ||p_Equipment. Number); WHEN OTHERS THEN v_Error. Text : = SQLERRM; DBMS_OUTPUT. PUT_LINE ('Unexpected error' ||v_Error. Text); END Update. Equipment; / Bordoloi and Bock

Example 13. 2 – Calls procedure of 13. 1 /* PL SQL Example 13. 2 File: ch 13 -2. sql */ DECLARE v_Equipment. Number%TYPE : = '5000'; v_Description Equipment. Description%TYPE : = 'Printer'; v_Cost Equipment. Original. Cost%TYPE : = 172. 00; v_Quantity Equipment. Quantity. Available%TYPE : = 2; v_Project Equipment. Project. Number%TYPE : = 5; BEGIN Update. Equipment(v_Equipment. Number, v_Description, v_Cost, v_Quantity, v_Project); END; / Bordoloi and Bock

Points to Understand About Update. Equipment Procedure • There are several points that you need to understand about calling a procedure and the use of parameters for this example. • The Update. Equipment procedure is first created, compiled, and stored in the database as a compiled object. • The actual parameters are declared within PL/SQL Example 13. 2 and assigned values – the assigned values here merely illustrate that the parameters would have values that are passed to the Update. Equipment procedure. • The calling statement is a PL/SQL statement by itself and is not part of an expression – control will pass from the calling statement to the first statement inside the procedure. • Because the formal parameters in Update. Equipment are all declared as mode IN, the values of these parameters cannot be changed within the procedure. Bordoloi and Bock

Example 13. 4 – Procedure with No Parameters /* PL SQL Example 13. 4 File: ch 13 -4. sql */ CREATE OR REPLACE PROCEDURE Display. Salary IS -- create local variable with required constraint temp_Salary NUMBER(10, 2); BEGIN SELECT Salary INTO temp_Salary FROM Employee WHERE Employee. ID = '01885'; IF temp_Salary > 15000 THEN DBMS_OUTPUT. PUT_LINE ('Salary > 15, 000. '); ELSE DBMS_OUTPUT. PUT_LINE ('Salary < 15, 000. '); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT. PUT_LINE ('Employee not found. '); END Display. Salary; / Bordoloi and Bock

Executing Display. Salary Procedure SQL> @ch 13 -4. sql Procedure created. SQL> exec Display. Salary > 15, 000. PL/SQL procedure successfully completed. Bordoloi and Bock

Example 13. 5 – Passing IN and OUT Parameters /* PL SQL Example 13. 5 File: ch 13 -5. sql */ CREATE OR REPLACE PROCEDURE Display. Salary 2(p_Employee. ID IN CHAR, p_Salary OUT NUMBER) IS v_Salary NUMBER(10, 2); BEGIN SELECT Salary INTO v_Salary FROM Employee WHERE Employee. ID = p_Employee. ID; IF v_Salary > 15000 THEN DBMS_OUTPUT. PUT_LINE ('Salary > 15, 000. '); ELSE DBMS_OUTPUT. PUT_LINE ('Salary <= 15, 000. '); END IF; p_Salary : = v_Salary; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT. PUT_LINE ('Employee not found. '); END Display. Salary 2; Bordoloi and Bock

Example 13. 6 – Calling Display. Salary 2 /* PL SQL Example 13. 6 File: ch 13 -6. sql */ DECLARE v_Salary. Output NUMBER : = 0; BEGIN -- call the procedure Display. Salary 2('01885', v_Salary. Output); -- display value of salary after the call DBMS_OUTPUT. PUT_LINE ('Actual salary: ' ||TO_CHAR(v_Salary. Output)); END; / Salary > 15, 000. Actual salary: 16250 PL/SQL procedure successfully completed. Bordoloi and Bock

Example 13. 7 – Using Bind Variables • Another approach to test a procedure. This approach uses a bind variable in Oracle. • A bind variable is a variable created at the SQL*Plus prompt that is used to reference variables in PL/SQL subprograms. • A bind variable used in this fashion must be prefixed with a colon “: ” – this syntax is required. /* PL SQL Example 13. 7 */ SQL> var v_Salary. Output NUMBER; SQL> EXEC Display. Salary 2('01885', : v_Salary. Output); Salary > 15, 000. PL/SQL procedure successfully completed. SQL> PRINT v_Salary. Output; V_SALARYOUTPUT -------16250 Bordoloi and Bock

Dropping a Procedure • The SQL statement to drop a procedure is the straight-forward DROP PROCEDURE command. • This is a data definition language (DDL) command, and so an implicit commit executes prior to and immediately after the command. SQL> DROP PROCEDURE Display. Salary 2; Procedure dropped. Bordoloi and Bock

Create Function Syntax • Like a procedure, a function can accept multiple parameters, and the data type of the return value must be declared in the header of the function. CREATE [OR REPLACE] FUNCTION ( , , . . . ) RETURN {AS|IS} BEGIN Executable Commands RETURN (return_value); . . . [EXCEPTION Exception handlers] END; • The general syntax of the RETURN statement is: RETURN ; Bordoloi and Bock

Example 13. 8 – No Parameters in Function /* PL SQL Example 13. 8 File: ch 13 -8. sql */ CREATE OR REPLACE FUNCTION Retrieve. Salary RETURN NUMBER IS v_Salary NUMBER(10, 2); BEGIN SELECT Salary INTO v_Salary FROM Employee WHERE Employee. ID = '01885'; RETURN v_Salary; END Retrieve. Salary; / Bordoloi and Bock

Example 13. 9 – Testing Retrieve. Salary Function /* PL SQL Example 13. 9 */ SQL> @Retrieve. Salary Function created. SQL> var v_Salary. Output NUMBER; SQL> EXEC : v_Salary. Output : = Retrieve. Salary; PL/SQL procedure successfully completed. SQL> print v_Salary. Output; V_SALARYOUTPUT -------16250 Bordoloi and Bock

Example 13. 9 (1 of 2)– Function with Parameter • PL/SQL Example 13. 9 illustrates a function that has a single IN parameter and that returns a VARCHAR 2 data type. /* PL SQL Example 13. 9 File: ch 13 -9. sql */ CREATE OR REPLACE FUNCTION Full. Name (p_Employee. ID IN employee. Employee. ID%TYPE) RETURN VARCHAR 2 IS v_Full. Name VARCHAR 2(100); v_First. Name employee. First. Name%TYPE; v_Middle. Name employee. Middle. Name%TYPE; v_Last. Name employee. Last. Name%TYPE; BEGIN SELECT First. Name, Middle. Name, Last. Name INTO v_First. Name, v_Middle. Name, v_Last. Name FROM Employee WHERE Employee. ID = p_Employee. ID; Bordoloi and Bock

Example 13. 9 (1 of 2)– Function with Parameter -- Store last name, comma and blank and first name to variable v_Full. Name : = v_Last. Name||', '||v_First. Name; -- Check for existence of a middle name IF LENGTH(v_Middle. Name) > 0 THEN v_Full. Name : = v_Full. Name|| ' ' ||SUBSTR(v_Middle. Name, 1, 1)||'. '; END IF; RETURN v_Full. Name; END Full. Name; / Bordoloi and Bock

Example 13. 10 – Testing Full. Name Function • A simple SELECT statement executed within SQL*Plus can return the full name for any employee identifier value as shown in PL/SQL Example 13. 10. /* PL SQL Example 13. 10 */ SQL> SELECT Full. Name('01885') 2 FROM Employee 3 WHERE Employee. ID = '01885'; FULLNAME('01885') -----------------Bock, Douglas B. Bordoloi and Bock

Example 13. 11 – Testing Full. Name Function /* PL SQL Example 13. 11 */ SQL> SELECT Full. Name(Employee. ID) 2 FROM Employee 3 ORDER BY Full. Name(Employee. ID); FULLNAME(EMPLOYEEID) ------------------Adams, Adam A. Barlow, William A. Becker, Robert B. Becker, Roberta G. Bock, Douglas B. . more rows will display Bordoloi and Bock

Dropping a Function • As with the DROP PROCEDURE statement, the DROP FUNCTION is also straight-forward. • As with DROP PROCEDURE, the DROP FUNCTION statement is a DDL command that causes execution of an implicit commit prior to and immediately after the command. SQL> DROP FUNCTION Full. Name; Function dropped. Bordoloi and Bock

PACKAGES • A package is a collection of PL/SQL objects grouped together under one package name. • Packages provide a means to collect related procedures, functions, cursors, declarations, types, and variables into a single, named database object that is more flexible than the related database objects are by themselves. • Package variables – can be referenced in any procedure, function, (other object) defined within a package. Bordoloi and Bock

Package Specification and Scope • A package consists of a package specification and a package body. – The package specification, also called the package header. – Declares global variables, cursors, exceptions, procedures, and functions that can be called or accessed by other program units. – A package specification must be a uniquely named database object. – Elements of a package can declared in any order. If element “A” is referenced by another element, then element “A” must be declared before it is referenced by another element. For example, a variable referenced by a cursor must be declared before it is used by the cursor. • Declarations of subprograms must be forward declarations. – This means the declaration only includes the subprogram name and arguments, but does not include the actual program code. Bordoloi and Bock

Create Package Syntax • Basically, a package is a named declaration section. – Any object that can be declared in a PL/SQL block can be declared in a package. – Use the CREATE OR REPLACE PACKAGE clause. – Include the specification of each named PL/SQL block header that will be public within the package. – Procedures, functions, cursors, and variables that are declared in the package specification are global. • The basic syntax for a package is: CREATE [OR REPLACE PACKAGE[ {AS|IS} ; ; ; END ; Bordoloi and Bock

Declaring Procedures and Functions within a Package • To declare a procedure in a package – specify the procedure name, followed by the parameters and variable types: PROCEDURE (param 1 datatype, param 2 datatype, . . . ); • To declare a function in a package, you must specify the function name, parameters and return variable type: FUNCTION (param 1 datatype, param 2 datatype, . . . ) RETURN ; Bordoloi and Bock

Package Body • Contains the code for the subprograms and other constructs, such as exceptions, declared in the package specification. • Is optional – a package that contains only variable declarations, cursors, and the like, but no procedure or function declarations does not require a package body. • Any subprograms declared in a package must be coded completely in the package body. The procedure and function specifications of the package body must match the package declarations including subprogram names, parameter names, and parameter modes. Bordoloi and Bock

Create Package Body Syntax • Use the CREATE OR REPLACE PACKAGE BODY clause to create a package body. The basic syntax is: CREATE [OR REPLACE] PACKAGE BODY AS END ; Bordoloi and Bock

Example 13. 12 – Example Package /* PL SQL Example 13. 12 File: ch 13 -12. sql */ CREATE OR REPLACE PACKAGE Manage. Employee AS -- Global variable declarations go here -- Procedure to find employees PROCEDURE Find. Employee( emp_ID IN employee. Employee. ID%TYPE, emp_First. Name OUT employee. First. Name%TYPE, emp_Last. Name OUT employee. Last. Name%TYPE); -- Exception raised by Find. Employee e_Employee. IDNot. Found EXCEPTION; -- Function to determine if employee identifier is valid FUNCTION Good. Identifier( emp_ID IN employee. Employee. ID%TYPE) RETURN BOOLEAN; END Manage. Employee; / Bordoloi and Bock

Example 13. 13 (1 of 2) – Package Body /* PL SQL Example 13. 13 File: ch 13 -13. sql */ CREATE OR REPLACE PACKAGE BODY Manage. Employee AS -- Procedure to find employees PROCEDURE Find. Employee( emp_ID IN employee. Employee. ID%TYPE, emp_First. Name OUT employee. First. Name%TYPE, emp_Last. Name OUT employee. Last. Name%TYPE ) AS BEGIN SELECT First. Name, Last. Name INTO emp_First. Name, emp_Last. Name FROM Employee WHERE Employee. ID = emp_ID; -- Check for existence of employee IF SQL%ROWCOUNT = 0 THEN RAISE e_Employee. IDNot. Found; END IF; END Find. Employee; Bordoloi and Bock

Example 13. 13 (2 of 2) – Package Body -- Function to determine if employee identifier is valid FUNCTION Good. Identifier( emp_ID IN employee. Employee. ID%TYPE) RETURN BOOLEAN IS v_ID_Count NUMBER; BEGIN SELECT COUNT(*) INTO v_ID_Count FROM Employee WHERE Employee. ID = emp_ID; -- return TRUE if v_ID_COUNT is 1 RETURN (1 = v_ID_Count); EXCEPTION WHEN OTHERS THEN RETURN FALSE; END Good. Identifier; END Manage. Employee; Bordoloi and Bock

Example 13. 14 – Calling Package Procedure /* PL SQL Example 13. 14 File: ch 13 -14. sql */ DECLARE v_First. Name employee. First. Name%TYPE; v_Last. Name employee. Last. Name%TYPE; search_ID employee. Employee. ID%TYPE; BEGIN Manage. Employee. Find. Employee (&search_ID, v_First. Name, v_Last. Name); DBMS_OUTPUT. PUT_LINE ('The employee name is: ' || v_Last. Name || ', ' || v_First. Name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT. PUT_LINE ('Cannot find an employee with that ID. '); END; / Bordoloi and Bock

Results of Calling Package Procedure • When the employee identifier is valid, the code displays the employee name as shown here. Enter value for search_id: '01885' The employee name is: Bock, Douglas PL/SQL procedure successfully completed. • When the identifier is not valid, the exception raised within the called procedure is propagated back to the calling procedure and is trapped by the EXCEPTION section’s WHEN OTHERS clause and an appropriate message is displayed as shown here. Enter value for search_id: '99999' Cannot find an employee with that ID. PL/SQL procedure successfully completed. Bordoloi and Bock

Cursors in Packages • A cursor variable can make a cursor dynamic so that it is reusable and sharable among different procedures and functions such as those created as part of a package. • A cursor variable has data type REF CURSOR. It is like a pointer in the C language, and it points to a query work area where a result set is stored. • First you must define a REF CURSOR type. • Next, you define a cursor variable of that type. In this general syntactic example, the object represents a row in a database table. TYPE ref_type_name IS REF CURSOR [RETURN ]; • This provides an example of declaring a cursor variable that can be used to process data rows for the equipment table of the Madison Hospital database. DECLARE TYPE equipment_Type IS REF CURSOR RETURN equipment%ROWTYPE; cv_Equipment IN OUT equipment_Type; Bordoloi and Bock

Example 13. 15 – REF CURSOR Type • The Package Specification declares a REF CURSOR type named equipment_Type and two procedures named Open. Item and Fetch. Item. • The cursor cv_Equipment in the Open. Item procedure is declared as an IN OUT parameter – it will store an equipment item after the procedure is executed—it is this stored value that is input to the Fetch. Item procedure. /* PL SQL Example 13. 15 File: ch 13 -15. sql */ CREATE OR REPLACE PACKAGE Manage. Equipment AS -- Create REF CURSOR type TYPE equipment_Type IS REF CURSOR RETURN equipment%ROWTYPE; -- Declare procedure PROCEDURE Open. Item (cv_Equipment IN OUT equipment_Type, p_Equipment. Number IN CHAR); -- Declare procedure to fetch an equipment item PROCEDURE Fetch. Item (cv_Equipment IN equipment_Type, equipment_Row OUT equipment%ROWTYPE); END Manage. Equipment; Bordoloi and Bock

Example 13. 16 – Package Body /* PL SQL Example 13. 16 File: ch 13 -16. sql */ CREATE OR REPLACE PACKAGE BODY Manage. Equipment AS -- Procedure to get a specific item of equipment PROCEDURE Open. Item (cv_Equipment IN OUT equipment_Type, p_Equipment. Number IN CHAR) AS BEGIN -- Populate the cursor OPEN cv_Equipment FOR SELECT * FROM Equipment WHERE Equipment. Number = p_Equipment. Number; END Open. Item; PROCEDURE Fetch. Item (cv_Equipment IN equipment_Type, equipment_Row OUT equipment%ROWTYPE) AS BEGIN FETCH cv_Equipment INTO equipment_Row; END Fetch. Item; END Manage. Equipment; Bordoloi and Bock

Example 13. 16 – Use Cursor Variable /* PL SQL Example 13. 16 File: ch 13 -16. sql */ DECLARE -- Declare a cursor variable of the REF CURSOR type item_Cursor Manage. Equipment. equipment_Type; v_Equipment. Number equipment. Equipment. Number%TYPE; equipment_Row equipment%ROWTYPE; BEGIN -- Assign a equipment number to the variable v_Equipment. Number : = '5001'; -- Open the cursor using a variable Manage. Equipment. Open. Item (item_Cursor, v_Equipment. Number); -- Fetch the equipment data and display it LOOP Manage. Equipment. Fetch. Item( item_Cursor, equipment_Row); EXIT WHEN item_cursor%NOTFOUND; DBMS_OUTPUT. PUT (equipment_Row. Equipment. Number || ' '); DBMS_OUTPUT. PUT_LINE (equipment_Row. Description); END LOOP; END; 5001 Computer, Desktop PL/SQL procedure successfully completed. Bordoloi and Bock

DATABASE TRIGGERS • Database trigger – a stored PL/SQL program unit that is associated with a specific database table, or with certain view types – can also be associated with a system event such as database startup. • Triggers execute (fire) automatically for specified SQL DML operations – INSERT, UPDATE, or DELETE affecting one or more rows of a table. • Database triggers can be used to perform any of the following tasks: – – – – Audit data modification. Log events transparently. Enforce complex business rules. Derive column values automatically. Implement complex security authorizations. Maintain replicate tables. Publish information about events for a publish-subscribe environment such as that associated with web programming. Bordoloi and Bock

Facts About Triggers • Triggers: – are named PL/SQL blocks with declarative, executable, and exception handling sections. – are stand-alone database objects – they are not stored as part of a package and cannot be local to a block. – do not accept arguments. • To create/test a trigger, you (not the system user of the trigger) must have appropriate access to all objects referenced by a trigger action. • Example: To create a BEFORE INSERT trigger for the employee table requires you to have INSERT ROW privileges for the table. Bordoloi and Bock

Trigger Limitations • Triggers cannot contain the COMMIT, ROLLBACK, and SAVEPOINT statements. • Trigger body – cannot exceed 32 K in size. • No limit on the number of triggers defined for a DML statement for a table. In fact, you can define two triggers of the same type for a table. When this occurs, the triggers of the same type fire sequentially. Bordoloi and Bock

Create Trigger Syntax CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE|AFTER|INSTEAD OF} triggering_event [referencing_clause] ON {table_name | view_name} [WHEN condition] [FOR EACH ROW] DECLARE Declaration statements [BEGIN Executable statements EXCEPTION Exception-handling statements] END; • • • The trigger body must have at least the executable section. The declarative and exception handling sections are optional. When there is a declarative section, the trigger body must start with the DECLARE keyword. • The WHEN clause specifies the condition under which a trigger should fire. Bordoloi and Bock

Trigger Types • BEFORE and AFTER Triggers – trigger fires before or after the triggering event. Applies only to tables. • INSTEAD OF Trigger – trigger fires instead of the triggering event. Applies only to views. • Triggering_event – a DML statement issued against the table or view named in the ON clause – example: INSERT, UPDATE, or DELETE. • DML triggers are fired by DML statements and are referred to sometimes as row triggers. • FOR EACH ROW clause – a ROW trigger that fires once for each modified row. • STATEMENT trigger – fires once for the DML statement. • Referencing_clause – enables writing code to refer to the data in the row currently being modified by a different name. Bordoloi and Bock

Example 13. 17 – STATEMENT Trigger /* PL SQL Example 13. 17 File: ch 13 -17. sql */ CREATE OR REPLACE TRIGGER Secure. Employee BEFORE DELETE OR INSERT OR UPDATE ON employee BEGIN IF (TO_CHAR(SYSDATE, 'day') IN ('saturday', 'sunday')) OR (TO_CHAR(SYSDATE, 'hh 24: mi') NOT BETWEEN '08: 30' AND '18: 30') THEN RAISE_APPLICATION_ERROR(-20500, 'Employee table is secured'); END IF; END; / • Trigger uses the RAISE_APPLICATION_ERROR statement to inform the application user that the table is secure and cannot be modified on a weekend day (Saturday or Sunday) or prior to 8: 30 a. m. or after 6: 30 p. m. Bordoloi and Bock

Testing the Secure. Employee Trigger SQL> UPDATE Employee SET Salary = 10 WHERE Employee. ID = '01885'; UPDATE Employee SET Salary = 10 WHERE Employee. ID = '01885' * ERROR at line 1: ORA-20500: table is secured ORA-06512: at "DBOCK. SECUREEMPLOYEE", line 4 ORA-04088: error during execution of trigger 'DBOCK. SECUREEMPLOYEE' Bordoloi and Bock

ROW Trigger – Accessing Rows • Access data on the row currently being processed by using two correlation identifiers named : old and : new. These are special Oracle bind variables. • The PL/SQL compiler treats the : old and : new records as records of type trigger_Table_Name%ROWTYPE. • To reference a column in the triggering table, use the notation shown here where the Column. Name value is a valid column in the triggering table. : new. Column. Name : old. Column. Name Bordoloi and Bock

Bind Variables : old and : new Defined DML Statement : old INSERT Undefined – all column Stores the values that will values are NULL as there be inserted into the new is no “old” version of the row for the table. data row being inserted. UPDATE Stores the original values for the row being updated before the update takes place. Stores the new values for the row – values the row will contain after the update takes place. DELETE Stores the original values for the row being deleted before the deletion takes place. Undefined – all column values are NULL as there will not be a “new” version of the row being deleted. Bordoloi and Bock : new

Audit Log Application – Equipment Table Example • Triggers can automate the creation of an audit log when a table is modified. • Create an Equipment_Audit table to store audit log records. /* PL SQL Example 13. 18 File: ch 13 -18. sql */ CREATE TABLE Equipment_Audit ( Action VARCHAR 2(10), Action. Date DATE DEFAULT SYSDATE, Equipment. Number CHAR(4), Description VARCHAR 2(25), Original. Cost NUMBER(7, 2), Quantity. Available NUMBER(4), Project. Number NUMBER(4)); Bordoloi and Bock

Example 13. 19 – Audit. Equipment Trigger /* PL SQL Example 13. 19 File: ch 13 -19. sql */ CREATE OR REPLACE TRIGGER Audit. Equipment AFTER DELETE OR INSERT OR UPDATE ON Equipment FOR EACH ROW BEGIN IF DELETING THEN INSERT INTO equipment_audit VALUES ('DELETE', SYSDATE, : old. Equipment. Number, : old. Description, : old. Original. Cost, : old. Quantity. Available, : old. Project. Number); ELSIF INSERTING THEN INSERT INTO equipment_audit VALUES ('INSERT', SYSDATE, : new. Equipment. Number, : new. Description, : new. Original. Cost, : new. Quantity. Available, : new. Project. Number); ELSE -- updating - Insert a before and after image of updates INSERT INTO equipment_audit VALUES ('UPDATE-OLD', SYSDATE, : old. Equipment. Number, : old. Description, : old. Original. Cost, : old. Quantity. Available, : old. Project. Number); INSERT INTO equipment_audit VALUES ('UPDATE-NEW', SYSDATE, : new. Equipment. Number, : new. Description, : new. Original. Cost, : new. Quantity. Available, : new. Project. Number); END IF; END; Bordoloi and Bock

Trigger Predicates • There are three trigger predicates that can be used to determine if a trigger is responding to a specific DML statement: INSERTING, UPDATING, and DELETING. • PL/SQL Example 13. 19 uses two of these in the IFELSIF-ELSE structure. • These predicates return TRUE if the triggering statement is of the type specified; otherwise, they return FALSE. • PL/SQL Example 13. 20 tests the Audit. Equipment trigger by inserting a new row, modifying the new row, and deleting the new row (next slide). Bordoloi and Bock

Example 13. 20 – Test Equipment. Audit Trigger /* PL SQL Example 13. 20 File: ch 13 -20. sql */ -- Insert new equipment row INSERT INTO Equipment VALUES('9000', 'X-Ray Table', 15500. 00, 1, 8); COMMIT; -- Modify equipment row UPDATE Equipment SET Quantity. Available = 2 WHERE Equipment. Number = '9000'; COMMIT; -- Delete equipment row DELETE FROM Equipment WHERE Equipment. Number = '9000'; COMMIT; -- List rows in Equipment_Audit table. SELECT * FROM Equipment_Audit; ACTIONDAT EQUI DESCRIPTION ----- -------INSERT 25 -NOV-07 9000 X-Ray Table UPDATE-OLD 25 -NOV-07 9000 X-Ray Table UPDATE-NEW 25 -NOV-07 9000 X-Ray Table DELETE 25 -NOV-07 9000 X-Ray Table Bordoloi and Bock ORIGINALCOST ------15500 QUAN ---1 1 2 2 PROJ ---8 8

WHEN Clause • The WHEN clause only applies to ROW triggers. • The body of the trigger executes only when the condition specified is met. • PL/SQL Example 13. 21 provides a partial outline for the logic of a trigger that includes a WHEN clause for high value items. • Note the seemingly inconsistent use of the : new bind variable in the WHEN clause – this syntax is correct – you do not specify the colon as part of the reference to the pseudo column. /* PL SQL Example 13. 21 */ CREATE OR REPLACE TRIGGER High. Cost BEFORE INSERT OR UPDATE OF Original. Cost ON equipment FOR EACH ROW WHEN (new. Original. Cost > 15000) BEGIN /* Trigger body action is coded here */ NULL; END; Bordoloi and Bock

Enabling and Disabling Triggers • It is useful to be able to enable and disable triggers; example, a script will bulk load the equipment table – firing a trigger during a bulk load for every row can degrade performance of the load. • By default, triggers are enabled. • A disabled trigger does not execute the trigger body even if the triggering statement is issued. The syntax for enabling and disabling triggers is: -- Disable an individual trigger by name. ALTER TRIGGER trigger_name DISABLE; -- Disable all triggers associated with a table. ALTER TABLE table_name DISABLE ALL TRIGGERS; -- Enable a trigger that was disabled. ALTER TRIGGER trigger_name ENABLE; -- Enable all triggers associated with a table. ALTER TABLE table_name ENABLE ALL TRIGGERS; Bordoloi and Bock

Dropping a Trigger • The DROP TRIGGER statement drops a trigger from the database. • If you drop a table, all associated table triggers are also dropped. • The syntax is: DROP TRIGGER trigger_name; Bordoloi and Bock

Summary • Created/replaced named procedures and functions. • Created packages that group PL/SQL types, variables, exceptions, and subprograms that are logically related. • Write PL/SQL blocks that called packages, procedures, and functions. • Write exception-handling code for the above objects. • Used different parameter types to pass values to/from a procedure and function. • Created triggers to manage complex business rules, establish special audit trails, and derive column values automatically. • Created both STATEMENT and ROW triggers and used triggers for a common database processing task, creating an audit trail to track changes made to the data stored in a table. Bordoloi and Bock