Using Oracle8
PL/SQL is a procedural language that allows the use of variable control structures such as IF...THEN_ELSE, FOR...LOOP, and so on, along with normal Structured Query Language commands (SQL). These types of programs are executed only once after every compilation. A sample PL/SQL program looks like the following:
declare
my_w_id number;
my_w_name varchar2(10);
my_w_street_1 varchar2(20);
my_w_street_2 varchar2(20);
my_w_city varchar2(20);
my_w_state char(2);
my_w_zip char(9);
begin
my_w_id := 1;
my_w_name := `Warehouse1';
my_w_street_1 := `9999 Int. Parkway';
my_w_street_2 := `Suite 700';
my_w_city := `Digital City';
my_w_state := `CY';
my_w_zip := `9999';
insert into warehouse values
(my_w_id,my_w_name,my_w_street_1,my_w_street_2,
my_w_city,my_w_state,my_w_zip);
end ;
Execute CATPROC.SQL before using procedures and functions Packages, procedures, and functions are part of Oracle's procedural option. You must run the CATPROC.SQL script from the SYS schema before using any of them.
This type of program is commonly known as an anonymous PL/SQL block because it isn't named. Because an anonymous block doesn't have a name, it can't be called by other programs. (Procedures and functions are also PL/SQL programs.) A procedure or a function lets you name a PL/SQL program and, with the assigned name, it can be called by other program units. It's stored within the database in compiled form and thus doesn't need to be compiled before every execution.
If you're familiar with any third-generation languages, by now you might have realized that procedures and functions are similar to procedures and functions in other third-generation languages. Procedures and functions together are known as subprograms. Subprograms have several advantages:
The only difference between procedures and functions is that functions can return values to the calling program through the RETURN statement, whereas procedures can't.
A procedure or a function has the following structure:
CREATE or REPLACE PROCEDURE procedure_name IS | AS
/* Declarative sections containing all */
*/ local variable declarations */
BEGIN
/* The executable PL/SQL code */
EXCEPTION
/* Exception handlers */
END ;
Figure A.1 shows the block diagram structure of a procedure and function.
FIGURE A.1 A procedure or function contains three sections. The syntax for creating a procedure or a function is as follows:
){kind=link}
CREATE [OR REPLACE] PROCEDURE procedure_name
[argument_1, mode, type],
[argument_2, mode, type],
...
[argument_n, mode, type] IS | AS
{procedural block} ;
Privileges required to create procedures, functions, and packages The CREATE PROCEDURE system privilege is required to create procedures, functions, and packages in a user's own schema. To create a trigger in someone else's schema, you need to have the CREATE ANY PROCEDURE system privilege.
REPLACE, which is an optional keyword, allows you to drop and recreate an existing procedure. It replaces the existing procedure without any warning and is useful in development and testing phases; however, it should be used with caution in a production environment where you may not want to replace an existing procedure without warning. The difference between dropping and recreating a procedure and using the CREATE command's REPLACE option is that the EXECUTE grants previously made on the procedure will remain when you use the REPLACE keyword.
argument_1, argument_2, and argument_n are the names of the \tvariables used to pass data between the calling program and the procedure. You can pass values to the input arguments and assign values to all output variables within the procedure. You can give any descriptive names to variables, except reserved words.
mode can be IN, OUT, or IN OUT. You assign IN mode to an \targument when its value will be passed to the procedure. \tOUT arguments are those whose values are assigned or calculated within the program and passed back to the calling program. IN OUT arguments are a combination of both; you pass the input value of an IN OUT argument to the procedure, manipulate it within the procedure, and assign the new value to be passed out to the calling program.
type specifies the datatype of the variable and can be any standard Oracle datatype or user-defined object type. These variables are unconstrained (their length isn't defined here). The datatypes get their length from the program environment where they're used, as shown in the following example:
CREATE OR REPLACE PROCEDURE ins_warehouse
(my_w_id IN number,
my_w_name IN varchar2,
my_w_street_1 IN varchar2,
my_w_street_2 IN varchar2,
my_w_city IN varchar2,
my_w_state IN char,
my_w_zip IN char) AS
begin
insert into warehouse values
(my_w_id,my_w_name,my_w_street_1,my_w_street_2,
my_w_city,my_w_state,my_w_zip);
end ;
A constrained data definition (such as VARCHAR2(20)) defines the length and type of the data element, whereas an unconstrained data definition (VARCHAR2) defines only the type, not the length. To use this procedure in a program, the PL/SQL code looks as follows:
execute ins_warehouse(2,'Warehouse2','8888 Int. Parkway', `Suite 700','Cyberspace','YH','12345');
You should have required access privileges on all the objects referred to inside the procedure. When a procedure is created, it can be executed by other users also, provided they've been given EXECUTE privilege on the procedure. (The EXECUTE privilege must be granted directly to users, not through roles.) The executing user doesn't need explicit privileges on the objects used in the procedure. As a result, you can implement very powerful data security. If you don't want users to access the database tables directly, you can set up an arrangement in which application users can access the tables referred to in the procedure only through the available procedures.
Preventing direct user access to database tables
The syntax for the CREATE FUNCTION command is similar to the CREATE PROCEDURE command:
CREATE [OR REPLACE] FUNCTION function_name
[argument_1, mode, type],
[argument_2, mode, type],
...
[argument_n, mode, type]
RETURN datatype
IS | AS
{procedural block} ;
Here, the RETURN datatype can be any valid PL/SQL datatype. The following example shows how to create and use a function named PYTHAGOREAN. It takes the length of two sides of a right-angled triangle and returns the length of the third side:
CREATE or REPLACE FUNCTION pythagorean
(sideone IN number, sidetwo IN number)
RETURN number AS
BEGIN
RETURN(sqrt(sideone*sideone + sidetwo*sidetwo));
end;
SQL> select pythagorean(3,4) from dual ;
PYTHAGOREAN(3,4)
----------------
5
Functions can be called in an SQL expression You can use functions in SQL statements in place of table columns or similarly to the built-in Oracle functions such as MOD, TO_NUMBER, and so on. However, the function shouldn't update the database and should contain only IN parameters.
Using %TYPE and %ROWTYPE for Datatypes It's a good practice to use %TYPE or %ROWTYPE while defining datatypes; that way you don't have to worry about the corresponding datatype in the tables. You also don't have to change the application code in case you change the column type in the table. The following examples show how to use %TYPE and \t%ROWTYPE datatypes for creating the ins_warehouse procedure.
%TYPE usage:
CREATE OR REPLACE PROCEDURE ins_warehouse (my_w_id IN warehouse.w_id%TYPE, my_w_name IN warehouse.w_name%TYPE, my_w_stock_value IN warehouse.w_stock_value%TYPE) AS my_exception EXCEPTION; begin insert into warehouse values (my_w_id,my_w_name,my_w_stock_value); end ;
%ROWTYPE usage:
CREATE OR REPLACE PROCEDURE ins_warehouse (my_w_row IN warehouse%ROWTYPE) AS my_exception EXCEPTION; begin insert into warehouse values (my_w_row.w_id, my_w_row.w_name, my_w_row.w_stock_value); end ;
You can use a PL/SQL block to insert a record by using the procedure with %ROWTYPE:
declare w_row warehouse%ROWTYPE;
begin
w_row.w_id:=6;
w_row.w_name := `Sixth';
w_row.w_stock_value := 600;
ins_warehouse(w_row);
commit ;
end;
Assigning Default Values to the Input Parameters A procedure's input parameters can also have default values. The syntax for assigning default values to the IN parameters is as follows:
CREATE OR REPLACE PROCEDURE ins_warehouse
(my_w_id IN warehouse.w_id%TYPE,
my_w_name IN warehouse.w_name%TYPE,
my_w_stock_value IN warehouse.w_stock_value%TYPE
DEFAULT 0) AS
begin
insert into warehouse values
(my_w_id,my_w_name,my_w_stock_value);
end ;
The IN parameters that have been assigned a default value don't need to be passed any value while calling the procedure. In the following example, notice that the value of the column W_STOCK_VALUE has been set to 0 in the inserted row:
SQL> execute ins_warehouse(7,'Seventh');
PL/SQL procedure successfully completed.
SQL> select * from warehouse ;
W_ID W_NAME W_STOCK_VALUE
---------- ---------- -------------
7 Seventh 0
Working with Exception Handlers Oracle raises an exception if it encounters an error condition while executing the PL/SQL code specified within a subprogram. If appropriate instructions are given to handle the raised exception, Oracle executes them. In case the exception handler isn't defined, the control is returned to the calling program without successful completion. Oracle doesn't return the value of the OUT parameters if a subprogram fails to execute.
An exception handler begins with a WHEN clause followed by \tthe name of the exception for which the handler is intended. Oracle provides a RAISE_APPLICATION_ERROR package to handle exceptions. With this package, you can return an error number and a message to the calling program, and the calling program can take appropriate action depending on the returned error. Some important predefined exceptions are as follows:
Predefined exceptions are raised automatically whenever Oracle encounters the error condition. You can also define exceptions by declaring them in the declarative section. A user-defined exception needs to be raised explicitly by the application code. Listing A.1 shows a procedure containing predefined exceptions and how the user-defined errors are displayed during its execution.
01: create table warehouse (
02: w_id number primary key,
03: w_name varchar2(10),
04: w_stock_value number(3)
05: );
06: CREATE OR REPLACE PROCEDURE ins_warehouse
07: (my_w_id IN number,
08: my_w_name IN varchar2,
09: my_w_stock_value IN number) AS
10: begin
11: insert into warehouse values
12: (my_w_id,my_w_name,my_w_stock_value);
13: exception
14: when DUP_VAL_ON_INDEX then
15: RAISE_APPLICATION_ERROR
16: (-20011,'Warehouse ID already present');
17: when OTHERS then
18: RAISE_APPLICATION_ERROR
19: (-20012,'Undefined Error');
20: end ;
21: SQL> execute ins_warehouse(1,'First',100);
22:
23: PL/SQL procedure successfully completed.
24:
25: SQL> execute ins_warehouse(1,'First',100);
26: begin ins_warehouse(1,'First',100); end;
27:
28: ERROR at line 1:
29: ORA-20011: Warehouse ID already present
30: ORA-06512: at "SCOTT.INS_WAREHOUSE", line 10
31: ORA-06512: at line 1
32:
33: SQL> execute ins_warehouse(1,'First',9100);
34: begin ins_warehouse(1,'First',9100); end;
35:
36: *
37: ERROR at line 1:
38: ORA-20012: Undefined Error
39: ORA-06512: at "SCOTT.INS_WAREHOUSE", line 13
41: ORA-06512: at line 1
Lines 14-19 show how to define error text to be displayed when the predefined exceptions DUP_VAL_ON_INDEX and OTHERS occur. The OTHERS exception (on line 17) is raised for all exceptions that aren't defined. Lines 29-41 show how these errors are displayed during an interactive SQL session.
Displaying descriptive error text Error conditions in lines 27-41 were simulated intentionally to show how user-defined error text is displayed when a predefined exception occurs.
Listing A.2 shows how to include a user-defined exception in a procedure.
Predefined and user-defined exceptions Listing A.1 shows how to use predefined exceptions, whereas Listing A.2 shows how to use user-defined exceptions. ("User" here means the application developer.)
01: CREATE OR REPLACE PROCEDURE ins_warehouse
02: (my_w_id IN number,
03: my_w_name IN varchar2,
04: my_w_stock_value IN number) AS
05: my_exception EXCEPTION;
06: begin
07: IF my_w_stock_value > 999 then
08: raise my_exception;
09: end if;
10: insert into warehouse values
11: (my_w_id,my_w_name,my_w_stock_value);
12: exception
13: when my_exception then
14: RAISE_APPLICATION_ERROR
15: (-20011, `Stock Value too large');
16: when DUP_VAL_ON_INDEX then
17: RAISE_APPLICATION_ERROR
18: (-20012,'Warehouse ID already present');
19: when OTHERS then
20: RAISE_APPLICATION_ERROR
21: (-20013,'Undefined Error');
22: end ;
Notice the declaration of the user-defined exceptions just before the begin keyword on line 6. Also note that the use of the DECLARE keyword isn't allowed here as is allowed in an anonymous PL/SQL block.
This is how the execution result looks when Listing A.2's user-defined exception is encountered:
SQL> execute ins_warehouse(1,'First',9100); begin ins_warehouse(1,'First',9100); end; ERROR at line 1: ORA-20011: Stock Value too large ORA-06512: at "SCOTT.INS_WAREHOUSE", line 14 ORA-06512: at line 1
Using the USER_ERRORS View If you're creating a package, function, or procedure through SQL*Plus, you can use the SHOW ERROR command to view the most recent errors encountered while creating or compiling a procedural object. These errors are also stored in the data dictionary and are accessible via the USER_ERRORS view. This view has the following structure:
SQL> desc user_errors Name Null? Type ------------------------------- -------- ---- NAME NOT NULL VARCHAR2(30) TYPE VARCHAR2(12) SEQUENCE NOT NULL NUMBER LINE NOT NULL NUMBER POSITION NOT NULL NUMBER TEXT NOT NULL VARCHAR2(4000)
It stores the last errors for all the procedures while creating them. Errors associated with a subprogram are removed only when the procedure is created successfully after fixing the cause of the errors, or the procedure is dropped by using the DROP \tPROCEDURE command. The DBA_ERRORS and ALL_ERRORS data dictionary views contain similar information about all the user errors in the database.
Viewing Source Code for Procedures and Functions The data dictionary view USER_SOURCE contains information about the procedure, function, and packages owned by the user. The view has the following structure:
SQL> desc user_source Name Null? Type ------------------------------- -------- ---- NAME NOT NULL VARCHAR2(30) TYPE VARCHAR2(12) LINE NOT NULL NUMBER TEXT VARCHAR2(4000)
A query similar to the following can be issued in order to view the source code for the procedure ins_warehouse:
SQL> select text from user_source
2 where name='INS_WAREHOUSE'
3 and type='PROCEDURE'
4 order by line ;
TEXT
-----------------------------------------------------------
PROCEDURE ins_warehouse
(my_w_id IN warehouse.w_id%TYPE,
my_w_name IN warehouse.w_name%TYPE,
my_w_stock_value IN warehouse.w_stock_value%TYPE
DEFAULT 0) AS
begin
insert into warehouse values
(my_w_id,my_w_name,my_w_stock_value);
end ;
9 rows selected.
SQL>
Hiding the Source Code by Using a Wrapper As described in the preceding section, you can access the procedures' and functions' source code from the data dictionary views. If you don't want end users to view the procedural objects' source code, use Oracle's Wrapper utility. You can convert any valid SQL or PL/SQL command file by using Wrapper. The utility's output is another file with the default extension .PLB. This file can still be executed from a SQL command line like any other SQL command. You can use the Wrapper utility to make the ins_warehouse procedure's source code invisible to end users:
$ wrap iname=ins_warehouse.sql oname=ins_warehouse.plb PL/SQL Wrapper: Release 8.0.4.0.0 - Production on Sat May 02 18:40:49 1998 Copyright Oracle Corporation 1993, 1994, 1995, 1996, 1997. All rights reserved Processing ins_warehouse.sql to ins_warehouse.plb$wrap ifile=ins_warehouse.sql ofile=ins_warehouse $ sqlplus scott/tiger SQL*Plus: Release 8.0.4.0.0 - Production on Sat May 2 18:42:0 1998 (c) Copyright 1997 Oracle Corporation. All rights reserved. Connected to: Oracle8 Enterprise Edition Release 8.0.4.0.0 - Production With the Partitioning and Objects options PL/SQL Release 8.0.4.0.0 - Production SQL> @ins_warehouse.plb Procedure created.
Now if you try to select the source code from the view USER_SOURCE, all you see is a bunch of rubbish. (Obviously it doesn't help to select from the view USER_SOURCE to find out what the procedure is doing because the source code isn't visible to you anymore):
SQL> select text from user_source 2 where name='INS_WAREHOUSE' 3 and type='PROCEDURE' 4 order by line ; TEXT ------------------------------------------------------------ PROCEDURE ins_warehouse wrapped 0 abcd abcd abcd ..... ..... .... 1 0: 0 0 0 33 2 0 1d 9a 8f :2 a0 6b :2 a0 f b0 3d 8f :2 a0 6b :2 a0 f b0 3d 8f :2 a0 6b :2 a0 f 51 b0 3d b4 55 6a :4 a0 5 d7 b2 5 e9 b7 a4 b1 11 68 4f 17 b5 33 2 0 3 4 2f 18 1c 20 23
Literally, the word package means a unit of commodity uniformly wrapped or sealed. The package in itself doesn't have any value but it enables you to present the product in a better fashion. Oracle packages are meant with a similar intent--they let you put related functions, procedures, and other items together. A package consists of two parts: specification and body. Using global variables
By using packages, you can define variables that will be available to package users--a very powerful way to define global variables. The variables defined in the package can be used by all the functions and procedures within the package. These variables can also be used outside the package by prefixing them with the package name.
A package specification contains specification of the procedures, functions, variables, cursors, exceptions, and constants that will be used within the package and will be available to its users. Package specifications are generally referred to as the package and consist of declarative sections. Anything that can go in the first section of Figure A.1--before the BEGIN statement of the PL/SQL block--can be put in the package. You need to use the following CREATE PACKAGE command to create a package:
CREATE [or REPLACE] PACKAGE package_name AS | IS procedure specification, function specification, variable specification, exception declaration, cursor declaration, type definition END package_name;
The following code creates a package named SAMPLE, which contains a procedure, a function, and an exception:
CREATE OR REPLACE PACKAGE sample AS
PROCEDURE ins_warehouse
(my_w_id IN warehouse.w_id%TYPE,
my_w_name IN warehouse.w_name%TYPE,
my_w_stock_value IN warehouse.w_stock_value%TYPE
DEFAULT 0);
FUNCTION pythagorean
(sideone IN number, sidetwo IN number)
RETURN number ;
my_exception EXCEPTION;
END sample;
When created, the package specification can viewed by describing the package as follows:
SQL> desc sample PROCEDURE INS_WAREHOUSE Argument Name Type In/Out Default? ------------------------ ------------------ ------ -------- MY_W_ID NUMBER IN MY_W_NAME VARCHAR2(10) IN MY_W_STOCK_VALUE NUMBER(3) IN DEFAULT FUNCTION PYTHAGOREAN RETURNS NUMBER Argument Name Type In/Out Default? ------------------------ ------------------ ------ -------- SIDEONE NUMBER IN SIDETWO NUMBER IN
A package body consists of PL/SQL blocks. It includes all the objects specified in the package specifications. Listing A.3 shows the code for creating a package body.
01: CREATE OR REPLACE PACKAGE BODY sample AS
02: PROCEDURE ins_warehouse
03: (my_w_id IN warehouse.w_id%TYPE,
04: my_w_name IN warehouse.w_name%TYPE,
05: my_w_stock_value IN warehouse.w_stock_value%TYPE
06: DEFAULT 0) AS
07: my_exception exception;
08: begin
09: IF my_w_stock_value > 999 then
10: raise my_exception;
11: end if;
12: insert into warehouse values
13: (my_w_id,my_w_name,my_w_stock_value);
14: exception
15: when my_exception then
16: RAISE_APPLICATION_ERROR
(-20011, `Stock Value too large');
17: when DUP_VAL_ON_INDEX then
18: RAISE_APPLICATION_ERROR
19: (-20012,'Warehouse ID already present');
20: when OTHERS then
21: RAISE_APPLICATION_ERROR
22: (-20013,'Undefined Error');
23: end ins_warehouse;
24: FUNCTION pythagorean
25: (sideone IN number, sidetwo IN number)
26: RETURN number AS
27: BEGIN
28: RETURN(sqrt(sideone*sideone + sidetwo*sidetwo));
29: end pythagorean;
30: end sample ;
Functions and programs defined within a package need to be referred to by prefixing the package names before them. This is how you'll refer to the ins_warehouse procedure defined in the sample package:
execute sample.ins_warehouse(1,'First',100);
A subprogram depends on all objects it uses. You can use the data dictionary view USER_DEPENDENCIES to determine on which objects a subprogram depends. This view has the following structure:
SQL> desc user_dependencies Name Null? Type ------------------------------- -------- ---- NAME NOT NULL VARCHAR2(30) TYPE VARCHAR2(12) REFERENCED_OWNER VARCHAR2(30) REFERENCED_NAME VARCHAR2(64) REFERENCED_TYPE VARCHAR2(12) REFERENCED_LINK_NAME VARCHAR2(128) SCHEMAID NUMBER DEPENDENCY_TYPE VARCHAR2(4)
See the later section, "Triggers and Dependencies," for more information about how to use this view.
Triggers are PL/SQL programs associated with a DML activity in the database. They're executed when the user performs the DML activity with which they're associated. Here are the salient features of triggers:
Execute CATPROC.SQL before using triggers Because triggers are part of Oracle's procedural option, you must run the CATPROC.SQL script from the SYS schema before using them.
The DML actions include INSERT, UPDATE, and DELETE operations on a table. You can define multiple triggers on a DML action. Triggers can be divided in the following types depending on the execution frequency, the DML action with which a trigger is associated, and the scope of the trigger: BEFORE INSERT row BEFORE UPDATE statement
BEFORE UPDATE row BEFORE DELETE statement
BEFORE DELETE row AFTER INSERT statement
AFTER INSERT row AFTER UPDATE statement
AFTER UPDATE row AFTER DELETE statement
AFTER DELETE row INSTEAD OF row BEFORE INSERT statement INSTEAD OF statement After a trigger is created on a table, it's executed whenever the triggering statement is executed. The existing data in the table isn't affected by creation of the trigger, however.
Triggers are used by application developers to perform the following functions:
Although referential integrity is part of the Oracle database, a trigger can help to implement advanced referential integrity checks if your application requires them.
Look at the CREATE TRIGGER command syntax and associated \tconcepts:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
triggering_statement ON table_name
[REFERENCING OLD | NEW AS old | new
[FOR EACH ROW]
[WHEN trigger_condition]
trigger_action ;
Privileges required to create a trigger
The CREATE TRIGGER system privilege is required to create a trigger in your own schema. To create a trigger in someone else's schema, you need to have the CREATE ANY TRIGGER system privilege.
The REPLACE Option The REPLACE option drops and recreates an existing trigger without warning. It's generally useful during the application development phase, when you need to recreate the objects frequently.
Trigger Name A trigger name should be unique within the schema. Although you can have another package, procedure, function, or table within the schema with the same name, it's not recommended to have trigger names conflicting with the other objects and subprograms. A convenient scheme for naming triggers is to prefix the table name with two or more characters indicating the type of the trigger. For example, triggers on the WAREHOUSE table could be named as follows: Before INSERT trigger bi_warehouse
After DELETE trigger ad_warehouse After UPDATE and DELETE aud_warehouse trigger
The BEFORE and AFTER Options and OLD and NEW Values You can fire a trigger before or after the triggering statement's execution, depending on how you define the trigger. Triggers also let you refer the old and new column values during the execution. INSERT statements usually refer to new values; thus, if you need to set a column value in the inserted row, you would use a BEFORE INSERT trigger. The following example shows how to use the BEFORE INSERT trigger to set the value of the warehouse table's ins_date column:
create or replace trigger bi_warehouse
BEFORE INSERT ON warehouse
for each row
begin
:new.w_ins_date := sysdate ;
end ;
Using :new and :old values in the trigger Note the use of :new and :old in the INSERT and DELETE statements with the table's column names. The :old prefix lets you reference the column values as they existed before the transaction, whereas the :new prefix lets you reference the values as they will exist after the transaction. :new and :old values are valid for an UPDATE statement; however, only :new and :old values, respectively, are available for an INSERT and DELETE statement. It should be intuitive that :old and :new values are available only for the row-level triggers, not for the statement-level triggers.
AFTER statement triggers are generally used when you want to take action after the row is successfully modified. For example, the following trigger inserts a value of w_id in a delete_history table when a row is deleted from the warehouse table:
CREATE OR REPLACE TRIGGER ad_warehouse
AFTER DELETE ON warehouse
for each row
Begin
insert into delete_history values
(:old.w_id, sysdate);
end ;
The INSTEAD OF Trigger The INSTEAD OF trigger can be defined with object or relational views. This trigger allows you to define actions alternative to the triggering statement to be taken. Thus, an INSERT, UPDATE, or DELETE statement on a view can be directed to perform the desired operation on the underlying tables.
If you create a view based on a table by using a group by clause or by joining two relational tables, a direct DML operation on the view isn't supported. You can get around this problem by using the INSTEAD OF trigger, which lets you specify the alternate code to be executed when an INSERT, UPDATE, or DELETE operation is performed on the view. Object views based on the nested tables and other object tables are treated in a similar manner.
Triggering Statements: INSERT, UPDATE, and DELETE You can define a trigger to execute with an INSERT, UPDATE, or DELETE statement on a table. You can also fire the same trigger with more than one of these actions. When you combine triggers on multiple events into a single trigger, you can use additional predicates if required to separate actions according to the triggering events.
In previous examples you've seen how to create triggers separately for INSERT and UPDATE operations. Now you learn how to create one trigger for both statements. Note that the column ins_date is updated only during INSERT operations and that the column update_date is updated only during the UPDATE operations, due to the use of the IF INSERTING... and IF UPDATING... predicates:
create table warehouse (
w_id number primary key,
w_name varchar2(10),
w_stock_value number(3),
w_ins_date date,
w_update_date date,
w_upd_ins_by varchar2(10)
);
create or replace trigger biu_warehouse
BEFORE INSERT or UPDATE ON warehouse
for each row
Begin
IF INSERTING then :new.w_ins_date := sysdate ;
end if ;
IF UPDATING then :new.w_update_date := sysdate;
end if ;
:new.w_upd_ins_by := user;
end ;
You can associate a trigger with only one table. However, you can have multiple triggers associated with each triggering event. Oracle doesn't guarantee the execution order among the multiple triggers defined on the same event. If a desired execution order is needed, you should combine the multiple triggers into one trigger with the desired coding sequence.
The Table Name table_name specifies the name of the table on which the trigger will be created. You can create a trigger in a schema other than the table's schema. If you keep the administrative overheads in mind, however, it's not recommended.
Access privileges required on the table to create triggers You should either own the table or have the ALTER privilege for the specified table. The ALTER table privilege can be granted to other users by granting the specific table-level grants or through the ALTER ANY TABLE system privilege.
The REFERENCING Option New and old words aren't Oracle reserved words; hence, you can create tables named new and old. Now imagine creating triggers on these tables and then using the :old and :new values as explained in the section "The BEFORE and AFTER Options and old and new Values." Oracle lets you use the REFERENCING option in order to avoid the conflict between table names and :new and :old values in the triggering statement. The new values are referred to as new_value in the following trigger:
create or replace trigger bi_warehouse
BEFORE INSERT ON warehouse
REFERENCING new as new_value
for each row
begin
:new_value.w_ins_date := sysdate ;
end ;
If you don't create the tables named new and old, you shouldn't need to use this option in the CREATE TRIGGER command.
Row- and Statement-Level Triggers The FOR EACH ROW option specifies that the scope of the trigger is row level. A row-level trigger is executed for each row affected by the statement; thus, if an UPDATE and DELETE statement affects multiple table rows, the trigger will be executed once for each affected row.
On the other hand, a statement-level trigger is executed only once for the statement, irrespective of the number of rows affected by it. By default, the CREATE TRIGGER command creates statement-level triggers--if you don't specify FOR EACH ROW.
You've seen only row-level triggers. The following statement-level trigger updates the last DML date for the warehouse table:
CREATE OR REPLACE TRIGGER biud_warehouse
BEFORE INSERT or UPDATE or DELETE ON warehouse
begin
update DML_LOG set DML_DATE=sysdate
where table_name='WAREHOUSE';
end ;
You can define row- and statement-level triggers for an event. Row-level triggers find wider use in applications in general than do statement-level triggers.
The WHEN Clause and Trigger Condition The WHEN clause further specifies the triggering condition for the trigger to be executed. The triggering condition can be defined by using the new and old values of the affected row's columns, and can be associated only with row-level triggers. The following example uses a row-level trigger to insert w_id and stock_value in the high_stock_warehouse table when the stock_value for the inserted warehouse row is more than 1 \tmillion:
CREATE OR REPLACE TRIGGER bi_warehouse
BEFORE INSERT ON warehouse
FOR EACH ROW
WHEN (new.w_stock_value > 1000000)
begin
insert into high_stock_warehouse values
(:new.w_id,:new.w_stock_value);
end ;
Using the colon with old and new in the trigger The colon isn't used with the new value in the WHEN condition; it's used only in the code appearing in the body of the trigger.
Trigger Action A trigger_action, or the code to be executed as part of the trigger, is a PL/SQL block. It can consist of SQL statements and PL/SQL code, which in turn can call other stored procedures and functions. The PL/SQL code executed as the trigger action is usually referred to as the trigger body. The trigger body has the following restrictions:
An update trigger can be defined so that it's executed only when any specified columns are updated. SQL statements that update a table but don't update the column in the specified list won't execute the trigger. The following example updates the table stock_value_log only when the column w_stock_value in the warehouse table is updated:
CREATE OR REPLACE TRIGGER bu_warehouse
BEFORE UPDATE OF W_STOCK_VALUE ON warehouse
FOR EACH ROW
begin
insert into stock_value_log values
(:new.w_id,sysdate);
end ;
Note the use of :new in front of w_id. Because the value of this column hasn't changed, you can use :new or :old without any difference.
Oracle provides various data dictionary views that contain information about the triggers. The data dictionary view USER_TRIGGERS contains information about all the triggers you created:
SQL> desc user_triggers Name Null? Type ------------------------------- -------- ---- TRIGGER_NAME NOT NULL VARCHAR2(30) TRIGGER_TYPE VARCHAR2(16) TRIGGERING_EVENT VARCHAR2(26) TABLE_OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) REFERENCING_NAMES VARCHAR2(87) WHEN_CLAUSE VARCHAR2(4000) STATUS VARCHAR2(8) DESCRIPTION VARCHAR2(4000) TRIGGER_BODY LONG
You can also use the USER_OBJECTS view to find name and status information about the triggers in the present schema:
select * from user_objects where object_type='TRIGGER';
Exporting and importing triggers
Because triggers are closely tied with the table, they're exported with the table. When the Import utility creates the table, it also creates the associated triggers. If you import the data into an existing table, the triggers also are created during the import process. The data dictionary views DBA_TRIGGERS and ALL_TRIGGERS contain information about all database triggers and all triggers accessible to the user, respectively. The following query returns information about the BU_WAREHOUSE trigger:
column TRIGGER_NAME format a20;
column TRIGGER_TYPE format a20;
column TRIGGERING_EVENT format a10;
column TABLE_NAME format a10;
column TABLE_OWNER format a10;
column REFERENCING_NAMES format a35;
column WHEN_CLAUSE format a30;
column STATUS format a8;
column DESCRIPTION format a30;
column TRIGGER_BODY format a80;
set linesize 60;
select * from user_triggers
where trigger_name='BU_WAREHOUSE' ;
TRIGGER_NAME TRIGGER_TYPE TRIGGERING
-------------------- -------------------- ----------
TABLE_OWNE TABLE_NAME REFERENCING_NAMES
---------- ---------- -----------------------------------
WHEN_CLAUSE STATUS
------------------------------ --------
DESCRIPTION
------------------------------
TRIGGER_BODY
------------------------------------------------------------
BU_WAREHOUSE BEFORE EACH ROW UPDATE
SCOTT WAREHOUSE REFERENCING NEW AS NEW OLD AS OLD
ENABLED
bu_warehouse
BEFORE UPDATE OF W_STOCK_VALUE
ON warehouse
FOR EACH ROW
begin
insert into stock_value_log values
(:old.w_id,sysdate);
end ;
A trigger can depend on various objects, such as tables, views, stored procedures, and functions. You can use the USER_DEPENDENCIES data dictionary view to discover what objects a trigger depends on. The following query finds the dependencies of the BU_WAREHOUSE trigger:
select * from all_dependencies where type='TRIGGER' and name='BU_WAREHOUSE;
Whenever you perform any DDL operations on the objects referred in a trigger, the trigger is marked invalid. The following example shows the status of the BU_WAREHOUSE trigger after the STOCK_VALUE_LOG table is recreated:
SQL> set echo on SQL> @status SQL> column OBJECT_NAME format a20; SQL> column TRIGGER_NAME format a20; SQL> column STATUS format a8; SQL> select object_name,status 2 from user_objects where 3 object_name='BU_WAREHOUSE' and object_type='TRIGGER'; OBJECT_NAME STATUS -------------------- -------- BU_WAREHOUSE VALID SQL> select trigger_name,status 2 from user_triggers 3 where trigger_name='BU_WAREHOUSE'; TRIGGER_NAME STATUS -------------------- -------- BU_WAREHOUSE ENABLED SQL> drop table stock_value_log; Table dropped. SQL> create table stock_value_log 2 (w_id number, stock_change_date date); Table created. SQL> select object_name,status 2 from user_objects where 3 object_name='BU_WAREHOUSE' and object_type='TRIGGER'; OBJECT_NAME STATUS -------------------- -------- BU_WAREHOUSE INVALID SQL> select trigger_name,status 2 from user_triggers 3 where trigger_name='BU_WAREHOUSE'; TRIGGER_NAME STATUS -------------------- -------- BU_WAREHOUSE ENABLED SQL>
Note the difference between the STATUS columns of the USER_OBJECTS and USER_TRIGGERS data dictionary views. USER_OBJECTS shows the valid/invalid status, whereas USER_TRIGGERS shows the enabled/disabled status. When a trigger is invalid, it's automatically recompiled the next time they're invoked. You can also manually recompile a trigger by using the alter trigger command:
alter trigger BU_WAREHOUSE compile ;
When a trigger is created without any errors, by default it's in the ENABLED state. When enabled, the trigger body is executed whenever the triggering statement with which the trigger is associated executes. Under special processing needs, you might want to skip the trigger code while executing the triggering statement. This can be done by disabling the trigger temporarily by using the alter trigger command:
alter trigger trigger_name disable ;
Issue the following command to enable the trigger again:
alter trigger trigger_name enable ;
You can also disable and enable all the triggers associated with a table by using the following commands:
alter table table_name disable all triggers ; alter table table_name enable all triggers ;
You can view the status of an existing trigger in the STATUS column of the data dictionary views USER_TRIGGERS, DBA_TRIGGERS, and ALL_TRIGGERS.
Dropping triggers If you drop a table, the associated triggers are also dropped. You can use the Drop trigger command to permanently drop an existing trigger: Drop trigger trigger_name;. Similar to disable all triggers, no drop all triggers clause appears in the alter table command.
© Copyright, Macmillan Computer Publishing. All rights reserved.