Chapter 13 : procedures, functions, packages, and triggers learning Objectives

Chapter 13 : PROCEDURES, FUNCTIONS, PACKAGES, and TRIGGERS

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.

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.

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.

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.

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

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.

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

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.

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.

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 proSample (v_Variable NUMBER(2), ...)

• /* Valid parameter. */

• CREATE OR REPLACE PROCEDURE proSample (v_Variable NUMBER, ...)

Example 13.1 (1 of 2)

• /* PL SQL Example 13.1 File: ch13-1.sql */

• CREATE OR REPLACE PROCEDURE UpdateEquipment (

• p_EquipmentNumber IN Equipment.EquipmentNumber%TYPE,

• p_Description IN Equipment.Description%TYPE,

• p_Cost IN Equipment.OriginalCost%TYPE,

• p_Quantity IN Equipment.QuantityAvailable%TYPE,

• p_Project IN Equipment.ProjectNumber%TYPE )

• AS

• e_EquipmentNotFound EXCEPTION;

• v_ErrorTEXT VARCHAR2(512);

Example 13.1 (2 of 2)

• BEGIN

• UPDATE Equipment SET Description = p_Description,

• OriginalCost = p_Cost, QuantityAvailable =

• p_Quantity, ProjectNumber = p_Project

• WHERE EquipmentNumber = p_EquipmentNumber;

• IF SQL%ROWCOUNT = 0 THEN

• Raise e_EquipmentNotFound;

• END IF;

• EXCEPTION

• WHEN e_EquipmentNotFound THEN

• DBMS_OUTPUT.PUT_LINE ('Invalid Equipment Number: '

• ||p_EquipmentNumber);

• WHEN OTHERS THEN

• v_ErrorText := SQLERRM;

• DBMS_OUTPUT.PUT_LINE ('Unexpected error'

• ||v_ErrorText);

• END UpdateEquipment;

• /

Example 13.2 – Calls procedure of 13.1

• /* PL SQL Example 13.2 File: ch13-2.sql */

• DECLARE

• v_EquipmentNumber Equipment.EquipmentNumber%TYPE

• := '5000';

• v_Description Equipment.Description%TYPE

• := 'Printer';

• v_Cost Equipment.OriginalCost%TYPE := 172.00;

• v_Quantity Equipment.QuantityAvailable%TYPE := 2;

• v_Project Equipment.ProjectNumber%TYPE := 5;

• BEGIN

• UpdateEquipment(v_EquipmentNumber, v_Description,

• v_Cost, v_Quantity, v_Project);

• END;

• /

Points to Understand About UpdateEquipment Procedure

• There are several points that you need to understand about calling a procedure and the use of parameters for this example.

• The UpdateEquipment 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 UpdateEquipment 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 UpdateEquipment are all declared as mode IN, the values of these parameters cannot be changed within the procedure.

Example 13.4 – Procedure with No Parameters

• /* PL SQL Example 13.4 File: ch13-4.sql */

• CREATE OR REPLACE PROCEDURE DisplaySalary IS

• -- create local variable with required constraint

• temp_Salary NUMBER(10,2);

• BEGIN

• SELECT Salary INTO temp_Salary FROM Employee

• WHERE EmployeeID = '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 DisplaySalary;

• /

Executing DisplaySalary Procedure

• SQL> @ch13-4.sql

• Procedure created.

• SQL> exec DisplaySalary

• Salary > 15,000.

• PL/SQL procedure successfully completed.

Example 13.5 – Passing IN and OUT Parameters

• /* PL SQL Example 13.5 File: ch13-5.sql */

• CREATE OR REPLACE PROCEDURE DisplaySalary2(p_EmployeeID

• IN CHAR, p_Salary OUT NUMBER) IS

• v_Salary NUMBER(10,2);

• BEGIN

• SELECT Salary INTO v_Salary FROM Employee

• WHERE EmployeeID = p_EmployeeID;

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

Example 13.6 – Calling DisplaySalary2

• /* PL SQL Example 13.6 File: ch13-6.sql */

• DECLARE

• v_SalaryOutput NUMBER := 0;

• BEGIN

• -- call the procedure

• DisplaySalary2('01885', v_SalaryOutput);

• -- display value of salary after the call

• DBMS_OUTPUT.PUT_LINE ('Actual salary: '

• ||TO_CHAR(v_SalaryOutput));

• END;

• /

• Salary > 15,000.

• Actual salary: 16250

• PL/SQL procedure successfully completed.

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

• SQL> EXEC DisplaySalary2('01885', :v_SalaryOutput);

• Salary > 15,000.

• PL/SQL procedure successfully completed.

• SQL> PRINT v_SalaryOutput;

• V_SALARYOUTPUT

• --------------

• 16250

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

• Procedure dropped.

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 ;

Example 13.8 – No Parameters in Function

• /* PL SQL Example 13.8

• File: ch13-8.sql */

• CREATE OR REPLACE FUNCTION RetrieveSalary

• RETURN NUMBER

• IS

• v_Salary NUMBER(10,2);

• BEGIN

• SELECT Salary INTO v_Salary

• FROM Employee

• WHERE EmployeeID = '01885';

• RETURN v_Salary;

• END RetrieveSalary;

• /

Example 13.9 – Testing RetrieveSalary Function

• /* PL SQL Example 13.9 */

• SQL> @RetrieveSalary

• Function created.

• SQL> var v_SalaryOutput NUMBER;

• SQL> EXEC :v_SalaryOutput := RetrieveSalary;

• PL/SQL procedure successfully completed.

• SQL> print v_SalaryOutput;

• V_SALARYOUTPUT

• --------------

• 16250

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 VARCHAR2 data type.

• /* PL SQL Example 13.9 File: ch13-9.sql */

• CREATE OR REPLACE FUNCTION FullName (p_EmployeeID IN

• employee.EmployeeID%TYPE)

• RETURN VARCHAR2 IS

• v_FullName VARCHAR2(100);

• v_FirstName employee.FirstName%TYPE;

• v_MiddleName employee.MiddleName%TYPE;

• v_LastName employee.LastName%TYPE;

• BEGIN

• SELECT FirstName, MiddleName, LastName INTO

• v_FirstName, v_MiddleName, v_LastName

• FROM Employee

• WHERE EmployeeID = p_EmployeeID;

Example 13.9 (1 of 2)– Function with Parameter

• -- Store last name, comma and blank and first name to variable

• v_FullName := v_LastName||', '||v_FirstName;

• -- Check for existence of a middle name

• IF LENGTH(v_MiddleName) > 0 THEN

• v_FullName := v_FullName|| ' '

• ||SUBSTR(v_MiddleName,1,1)||'.';

• END IF;

• RETURN v_FullName;

• END FullName;

• /

Example 13.10 – Testing FullName 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 FullName('01885')
• 2 FROM Employee
• 3 WHERE EmployeeID = '01885';
• FULLNAME('01885')
• -----------------------------------
• Bock, Douglas B.

Example 13.11 – Testing FullName Function

• /* PL SQL Example 13.11 */
• SQL> SELECT FullName(EmployeeID)
• 2 FROM Employee
• 3 ORDER BY FullName(EmployeeID);
• FULLNAME(EMPLOYEEID)
• -------------------------------------
• Adams, Adam A.
• Barlow, William A.
• Becker, Robert B.
• Becker, Roberta G.
• Bock, Douglas B.
• ... more rows will display

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 FullName;
• Function dropped.

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.

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.

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
  ;

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
  (param1 param1datatype,

• param2 param2datatype, ...);

• To declare a function in a package, you must specify the function name, parameters and return variable type:

• FUNCTION (param1 param1datatype,

• param2 param2datatype, ...)

• RETURN ;

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.

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
  ;

Example 13.12 – Example Package

• /* PL SQL Example 13.12 File: ch13-12.sql */

• CREATE OR REPLACE PACKAGE ManageEmployee AS

• -- Global variable declarations go here

• -- Procedure to find employees

• PROCEDURE FindEmployee(

• emp_ID IN employee.EmployeeID%TYPE,

• emp_FirstName OUT employee.FirstName%TYPE,

• emp_LastName OUT employee.LastName%TYPE);

• -- Exception raised by FindEmployee

• e_EmployeeIDNotFound EXCEPTION;

• -- Function to determine if employee identifier is valid

• FUNCTION GoodIdentifier(

• emp_ID IN employee.EmployeeID%TYPE)

• RETURN BOOLEAN;

• END ManageEmployee;

• /

Example 13.13 (1 of 2) – Package Body

• /* PL SQL Example 13.13 File: ch13-13.sql */

• CREATE OR REPLACE PACKAGE BODY ManageEmployee AS

• -- Procedure to find employees

• PROCEDURE FindEmployee(

• emp_ID IN employee.EmployeeID%TYPE,

• emp_FirstName OUT employee.FirstName%TYPE,

• emp_LastName OUT employee.LastName%TYPE ) AS

• BEGIN

• SELECT FirstName, LastName

• INTO emp_FirstName, emp_LastName

• FROM Employee

• WHERE EmployeeID = emp_ID;

• -- Check for existence of employee

• IF SQL%ROWCOUNT = 0 THEN

• RAISE e_EmployeeIDNotFound;

• END IF;

• END FindEmployee;

Example 13.13 (2 of 2) – Package Body

• -- Function to determine if employee identifier is valid

• FUNCTION GoodIdentifier(

• emp_ID IN employee.EmployeeID%TYPE)

• RETURN BOOLEAN

• IS

• v_ID_Count NUMBER;

• BEGIN

• SELECT COUNT(*) INTO v_ID_Count

• FROM Employee

• WHERE EmployeeID = emp_ID;

• -- return TRUE if v_ID_COUNT is 1

• RETURN (1 = v_ID_Count);

• EXCEPTION

• WHEN OTHERS THEN

• RETURN FALSE;

• END GoodIdentifier;

• END ManageEmployee;

Example 13.14 – Calling Package Procedure

• /* PL SQL Example 13.14

• File: ch13-14.sql */

• DECLARE

• v_FirstName employee.FirstName%TYPE;

• v_LastName employee.LastName%TYPE;

• search_ID employee.EmployeeID%TYPE;

• BEGIN

• ManageEmployee.FindEmployee (&search_ID, v_FirstName,

• v_LastName);

• DBMS_OUTPUT.PUT_LINE ('The employee name is: ' ||

• v_LastName || ', ' || v_FirstName);

• EXCEPTION

• WHEN OTHERS THEN

• DBMS_OUTPUT.PUT_LINE ('Cannot find an employee with that ID.');

• END;

• /

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.

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;

Example 13.15 – REF CURSOR Type

• The Package Specification declares a REF CURSOR type named equipment_Type and two procedures named OpenItem and FetchItem.

• The cursor cv_Equipment in the OpenItem 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 FetchItem procedure.

• /* PL SQL Example 13.15 File: ch13-15.sql */

• CREATE OR REPLACE PACKAGE ManageEquipment AS

• -- Create REF CURSOR type

• TYPE equipment_Type IS REF CURSOR

• RETURN equipment%ROWTYPE;

• -- Declare procedure

• PROCEDURE OpenItem (cv_Equipment IN OUT equipment_Type,

• p_EquipmentNumber IN CHAR);

• -- Declare procedure to fetch an equipment item

• PROCEDURE FetchItem (cv_Equipment IN equipment_Type,

• equipment_Row OUT equipment%ROWTYPE);

• END ManageEquipment;

Example 13.16 – Package Body

• /* PL SQL Example 13.16 File: ch13-16.sql */

• CREATE OR REPLACE PACKAGE BODY ManageEquipment AS

• -- Procedure to get a specific item of equipment

• PROCEDURE OpenItem (cv_Equipment IN OUT equipment_Type,

• p_EquipmentNumber IN CHAR) AS

• BEGIN

• -- Populate the cursor

• OPEN cv_Equipment FOR

• SELECT * FROM Equipment

• WHERE EquipmentNumber = p_EquipmentNumber;

• END OpenItem;

• PROCEDURE FetchItem (cv_Equipment IN equipment_Type,

• equipment_Row OUT equipment%ROWTYPE) AS

• BEGIN

• FETCH cv_Equipment INTO equipment_Row;

• END FetchItem;

• END ManageEquipment;

Example 13.16 – Use Cursor Variable

• /* PL SQL Example 13.16 File: ch13-16.sql */

• DECLARE

• -- Declare a cursor variable of the REF CURSOR type

• item_Cursor ManageEquipment.equipment_Type;

• v_EquipmentNumber equipment.EquipmentNumber%TYPE;

• equipment_Row equipment%ROWTYPE;

• BEGIN

• -- Assign a equipment number to the variable

• v_EquipmentNumber := '5001';

• -- Open the cursor using a variable

• ManageEquipment.OpenItem (item_Cursor, v_EquipmentNumber);

• -- Fetch the equipment data and display it

• LOOP

• ManageEquipment.FetchItem( item_Cursor, equipment_Row);

• EXIT WHEN item_cursor%NOTFOUND;

• DBMS_OUTPUT.PUT (equipment_Row.EquipmentNumber || ' ');

• DBMS_OUTPUT.PUT_LINE (equipment_Row.Description);

• END LOOP;

• END;

• 5001 Computer, Desktop

• PL/SQL procedure successfully completed.

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.

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.

Trigger Limitations

• Triggers cannot contain the COMMIT, ROLLBACK, and SAVEPOINT statements.

• Trigger body – cannot exceed 32K 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.

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.

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.

Example 13.17 – STATEMENT Trigger

• /* PL SQL Example 13.17 File: ch13-17.sql */

• CREATE OR REPLACE TRIGGER SecureEmployee

• BEFORE DELETE OR INSERT OR UPDATE ON employee

• BEGIN

• IF (TO_CHAR(SYSDATE, 'day') IN ('saturday', 'sunday')) OR

• (TO_CHAR(SYSDATE, 'hh24: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.

Testing the SecureEmployee Trigger

• SQL> UPDATE Employee SET Salary = 10

• WHERE EmployeeID = '01885';

• UPDATE Employee SET Salary = 10 WHERE EmployeeID = '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'

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 ColumnName value is a valid column in the triggering table.

• :new.ColumnName
• :old.ColumnName

Bind Variables :old and :new Defined

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: ch13-18.sql */

• CREATE TABLE Equipment_Audit (

• Action VARCHAR2(10),

• ActionDate DATE DEFAULT SYSDATE,

• EquipmentNumber CHAR(4),

• Description VARCHAR2(25),

• OriginalCost NUMBER(7,2),

• QuantityAvailable NUMBER(4),

• ProjectNumber NUMBER(4));

Example 13.19 – AuditEquipment Trigger

• /* PL SQL Example 13.19 File: ch13-19.sql */

• CREATE OR REPLACE TRIGGER AuditEquipment

• AFTER DELETE OR INSERT OR UPDATE ON Equipment

• FOR EACH ROW

• BEGIN

• IF DELETING THEN

• INSERT INTO equipment_audit VALUES ('DELETE', SYSDATE,

• :old.EquipmentNumber, :old.Description, :old.OriginalCost,

• :old.QuantityAvailable, :old.ProjectNumber);

• ELSIF INSERTING THEN

• INSERT INTO equipment_audit VALUES ('INSERT', SYSDATE,

• :new.EquipmentNumber, :new.Description, :new.OriginalCost,

• :new.QuantityAvailable, :new.ProjectNumber);

• ELSE -- updating - Insert a before and after image of updates

• INSERT INTO equipment_audit VALUES ('UPDATE-OLD', SYSDATE,

• :old.EquipmentNumber, :old.Description, :old.OriginalCost,

• :old.QuantityAvailable, :old.ProjectNumber);

• INSERT INTO equipment_audit VALUES ('UPDATE-NEW', SYSDATE,

• :new.EquipmentNumber, :new.Description, :new.OriginalCost,

• :new.QuantityAvailable, :new.ProjectNumber);

• END IF;

• END;

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 IF-ELSIF-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 AuditEquipment trigger by inserting a new row, modifying the new row, and deleting the new row (next slide).

Example 13.20 – Test EquipmentAudit Trigger

• /* PL SQL Example 13.20 File: ch13-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 QuantityAvailable = 2

• WHERE EquipmentNumber = '9000';

• COMMIT;

• -- Delete equipment row

• DELETE FROM Equipment WHERE EquipmentNumber = '9000';

• COMMIT;

• -- List rows in Equipment_Audit table.

• SELECT * FROM Equipment_Audit;

• ACTION ACTIONDAT EQUI DESCRIPTION ORIGINALCOST QUAN PROJ

• ---------- --------- ---- ------------ ------------ ---- ----

• INSERT 25-NOV-07 9000 X-Ray Table 15500 1 8

• UPDATE-OLD 25-NOV-07 9000 X-Ray Table 15500 1 8

• UPDATE-NEW 25-NOV-07 9000 X-Ray Table 15500 2 8

• DELETE 25-NOV-07 9000 X-Ray Table 15500 2 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 HighCost
• BEFORE INSERT OR UPDATE OF OriginalCost
• ON equipment
• FOR EACH ROW
• WHEN (new.OriginalCost > 15000) BEGIN
• /* Trigger body action is coded here */
• NULL;
• END;

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;

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;

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.