Defining instead-of triggers over nested collection columns...

Details Defining instead-of triggers over nested collection columns... Defining instead-of triggers over nested collection columns...

2000-02-24

2003-05-13

Robinson, Greta (Department: 2177)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000, C707S793000, C707S793000, C707S793000, C707S793000

Reexamination Certificate

active

06564203

ABSTRACT:

FIELD OF THE INVENTION
The present invention relates to database systems and, more particularly, to defining instead-of triggers over nested collection columns of views.
BACKGROUND OF THE INVENTION
In a database management system (DBMS), data is stored in one or more data containers, each container contains records, and the data within each record is organized into one or more fields. In relational database systems, the data containers are referred to as tables, the records are referred to as rows, and the fields are referred to as columns. In object oriented databases, the data containers are referred to as object classes, the records are referred to as objects, and the fields are referred to as attributes. Other database architectures may use other terminology.
Systems that implement the present invention are not limited to any particular type of data container or database architecture. However, for the purpose of explanation, the examples and the terminology used herein shall be that typically associated with relational databases. Thus, the terms “table”, “row” and “column” shall be used herein to refer respectively to the data container, record, and field.
A nested collection column of a table is a column that logically stores data items that belong to a collection data type. The collection of data items that logically reside within a row of a nested collection column is referred to as a nested collection. Nested collections may be ordered or unordered. Unordered nested collections are also referred to as nested tables. A table that has a nested collection column is referred to as the “parent table” of the nested collections that logically reside within the nested collection column.
Tables with nested collection columns can be created, for example, using a SELECT query and a CAST-MULTISET operator. For example, consider a department-employee scenario, where the table “emp” defines employee attributes and the table “dept” defines department attributes. Assume that these tables are defined as follows:
CREATE TABLE dept (
deptno NUMBER PRIMARY KEY,
deptname VARCHAR2 (20)
) ;
CREATE TABLE emp (
empno NUMBER,
empname VARCHAR2 (20),
salary NUMBER,
deptno NUMBER REFERENCES dept (deptno)
) ;
Given the above-listed definitions for the emp and dept tables, the CAST-MULTISET operator may be used to create instances of the “emp_list_t” type defined below, which represents a collection of the employee type “emp_t”.
CREATE TYPE emp_t AS OBJECT (
eno NUMBER,
ename VARCHAR2 (20),
salary NUMBER
) ;
/
CREATE TYPE emp_list_t AS TABLE OF emp_t;
/
Using the data types defined above, a table dept_emp may be created that has a row for each department, where the row for a particular department has one column for the department number, one column for department name, and one nested collection column that includes the names of all the employees that belong to the department.
CREATE TABLE dept_emp AS
SELECT d.deptno, d.deptname,
CAST (MULTISET (
SELECT e.empno, e.empname, e.salary
FROM emp e
WHERE e.deptno = d.deptno
) AS emp_list_t) emplist
FROM dept d;
Individual items in the nested collections of dept_emp can be modified using the TABLE( ) clause. For example, the following statement could be used to insert a row for “John” into the nested collection associated with the dept_emp table row for department
10
:
INSERT INTO TABLE (select emplist FROM dept_emp
WHERE deptno=10)
VALUES (10, ‘John’, 78000) ;
In this statement, the SELECT statement within the TABLE( ) clause identifies a specific row of the parent table (the row associated with department number
10
). By identifying a specific row in the parent table, the clause effectively identifies a specific instance of the nested collection. Having identified a specific instance of the nested collection, the database server is able to determine how the corresponding base table (emp) must be updated.
In many cases, it may not be desirable for all users to have access to the data in the salary column of the emplist collections. To implement a security policy that selectively limits access to columns, a table that contains sensitive information, such as the dept_emp table, is usually not made available for direct user access. Rather, the data would typically be made available through the use of one or more views.
VIEWS
A view is a logical table. As logical tables, views may be queried by users as if they were a table. However, views actually present data that is extracted or derived from existing tables. For security reasons, views are often used to provide users with access to a subset of the data that is actually stored in the database system. Columns that contain sensitive information may be included in the views made available to one set of users, and not included in the views made available to another set of users.
A view is defined by metadata referred to as a view definition. The view definition contains mappings to one or more columns in the one or more tables containing the data. Columns and tables that are mapped to a view are referred to herein as base columns and base tables of the view, respectively.
Typically, the view definition is in the form of a database query. For example, a view dept_view that has a row for each department, where the row for a particular department has one column for the department number, one column for department name, and one nested collection column that includes the names of all the employees that belong to the department, may be defined by the following statement:
CREATE VIEW dept_view AS
SELECT d.deptno, d.deptname,
CAST (MULTISET (
SELECT e.empno, e.empname, e.salary
FROM emp e
WHERE e.deptno = d.deptno
) AS emp_list_t) emplist
FROM dept d;
A view that may be created in response to this definition is illustrated in FIG.
1
. Each row of the dept_view has three columns. Within each row, the emplist column has a nested collection. In dept_view
100
, each nested collection is a virtual table that contains three columns. The structure of dept_view
100
is dictated by its view definition. The data that populates dept_view
100
is the data that resides in the based tables used to generate dept_view
100
.
One type of view that is generally not updateable is a view that is used to present data in the form of a virtual table with a nested collection column. The base data for the nested collections, and for the other columns of the virtual parent table that contains the nested collections, may actually have been gathered by the database server from multiple base tables. This characteristic of views with nested collections generally renders the views capable of being directly updated. However, other types of views that are not directly dateable may be rendered updateable through the use of a type of trigger that is known as an “instead-of” trigger.
TRIGGERS
In a database management system, a trigger is an object that specifies a series of actions to be automatically performed when a specific event occurs. According to industry standards, Data Manipulation Language (DML) statements are the events that cause user-defined triggers to be activated (or “fired”). For example, in a relational database, user-defined triggers may be designed to fire when a row of a database table or a table view is updated, inserted, or deleted. Accordingly, each user-defined trigger is typically associated with a single database table. That is, in a conventional database management system, the scope of the user-defined trigger is the table level of the database.
The series of actions specified by a trigger is typically written as instructions in a high-level database language such as SQL or PL/SQL (a procedural language extension of SQL available from Oracle Corporation of Redwood Shores, Calif.). In conformance with industry standards, these instructions must be able to access the data values of table columns corresponding to an affected row before the triggering DML statement was applied (the “old values”) and after the modification was applied (the “new values”).
Since triggers are objects, database customers can

Affiliated with

Also associated with

Rating

Defining instead-of triggers over nested collection columns... does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Defining instead-of triggers over nested collection columns..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Defining instead-of triggers over nested collection columns... will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-3088605