Understanding Triggers in Oracle PL/SQL: Mechanisms, Design, and Examples - Prof. Markus P, Study notes of Computer Science

Download Understanding Triggers in Oracle PL/SQL: Mechanisms, Design, and Examples - Prof. Markus P and more Study notes Computer Science in PDF only on Docsity!

What you should have learned after this lecture ...

Trigger in PL/SQL ‰

fundamentals of

database application programming

Trigger ‰

A

trigger − is a user-defined procedure which is automatically executed from the DBMS if acertain condition is fulfilled or as a side effect of a modification of the database. − answers to events with respect to a given relation. − is a general and powerful mechanism for maintaining data consistency. − can take not only check functions but also computation functions. − can update statistics, for example, or compute the values of derived columns. − consists of a head and a PL/SQL block. − contains in its head preconditions for executing the block. − is not part of SQL92. We orient ourselves to Oracle. ‰

Two requirements for the design of a trigger mechanism^ −

specification of conditions when the trigger is to be executed − specification of the actions that have to be performed if a trigger is executed

trigger event^ update

[ of

<column

, column 1

, ...>] 2

on

insert

on

delete

on

A trigger can be defined for one or several events. In case of several events, a casedistinction can be expressed in the body through the clauses: if updating

[<column

, column 1

, ...>] 2

then

if inserting then

if deleting then

trigger type[

for each row

]

−^

Command-oriented

trigger (default) are released exactly once either before

( before

) or after (

after

) the respective event.

−^

Row-oriented

trigger are called for each changed tuple. In the body one has the

possibility (and only this one) to address the old resp. the new value of the tupelsof the relation over

:old

or

:new

for

update

, over

:new

für

insert

and over

:old

for

delete

Another access to the relation is not possible any more, even if the relation wouldbe addressed in the respective block.

‰

trigger restriction^ when

−^

Conditions can be formulated which release the execution of the trigger body. −^

If a row-oriented trigger is used, the new resp. old tuple of the relation can beaddressed by the keywords

new

resp.

old

Trigger body ‰

procedure definition as PL/SQL-Block with the aforementioned extensions Examples ‰

Protocol of the changes of the attribute

salary

of a relation

Persons

create trigger

StoreSalary

before update on

Persons

for each row when

(:old.salary > 1500)

begin insert into

diff

values

(:old.salary, :new.salary,

sysdate

)^ end

Integrity constraints in Query-By-Example

Model inherent integrity concepts ‰

QBE supports key integrity and domain constraints ‰

check of key integrity when inserting a data record ‰

change of key attributes impossible Explicit integrity constraints ‰

For each relation a “constraint table” exists in which the ICs can be inserted as rows. is a list of elements out of {I., U., D.} as well as possibly user-definedtriggers. The columns can contain entries of the form

θ

c

(θ

comparison operator,

c

constant), also example elements for links with other tupels, and simple constants.

R^

Attr

1

Attr

2

Attr

n

I.CONSTR().I.

example: No balance may fall under USD -100. ‰

The account of Jones may not be overchecked. ‰

For each order the customer name must be contained in the relation

customer

and

the product must be contained in the relation

supplier

customer

cname

caddr

account

I.CONSTR(I., U.).I.

≥^

customer

cname

caddr

account

I.CONSTR(I., U.).I.

Jones

≥^

order

cname

product

price

I.CONSTR(I.).I.

_N

_W

customer

cname

caddr

account

_N

supplier

sname

saddr

product

price

_W

8. Application Programming

Introduction Database and programming languages ‰

SQL is a powerful declarative query language. The formulation of queries in SQL isusually simpler than the coding of the same queries in an all-purpose programminglanguage. There are at least two reasons for accessing a database with a program-ming language from a user perspective:^ −

Not all queries can be expressed in SQL (little functionality for “everyday” pro-gramming) since SQL does not have the full expressive power of a programminglanguage. In order to be able to express such queries, SQL can be embedded intoa more powerful language. Applications are usually developed in imperative andobject-oriented languages (C, Cobol, Fortran, Java, C++, ...). − Non-declarative actions like printing, interaction with the user, or transmission ofquery results to a graphical user interface are outside of SQL. Task sharing: queryprocessing and updates with SQL, all other tasks with the aid of a programminglanguage.

SQL queries can be automatically optimized and efficiently executed. The use of aprogramming language only makes an automatic optimization extraordinarily difficult. ‰

Ad hoc queries are posed mostly by experts and more seldom. Frequently non-inter-active batch applications are needed. Often the possibilities of the DBMS for repre-senting data are limited and unsuitable for user requirements. ‰

Special integration problem (

impedance mismatch

−^

programming language supports the processing of single data records (

tuple-ori-

ented approach

−^

SQL supports the processing of data records, i.e., of relations (

set-oriented

approach

consequently the question: How can the programming of database tasks be com-bined with the “usual” tasks without abandoning the benefits of SQL?