DATA INTEGRITY - Database Design and Development: An Essential Guide for IT Professionals - page 448

CREATE TRIGGER PARTWEIGHT

INSERT, UPDATE (PartWeight)
PART

BEFORE
ON
FOR EACH ROW
DECLARE

BigPartWeight

PART.PartWeight%type ;

BEGIN

SELECT PartWeight INTO BigPartWeight

FROM PART

WHERE PartName = 'BigPart' ;

:new.PartWeight > BigPartWeight
:old.PartName

IF AND

THEN

'BigPart'
RAISE APPLICATION ERROR (-20200,
'Part cannot have overweight' ) ;

< >

ENDIF ;

END ;

Figure 15-7 Sample trigger module.

INVENTORY RECORDS
IN DATABASE

Order Entry --
Chicago

Order Entry --
Boston

DB DW

Order: Database textbook
70 copies

Order: Data Warehouse textbook
60 copies

200 300

BEGIN
1. Read DB inventory (200)
2. Enter Order
3. Update DB inventory (130)
END

BEGIN
1. Read DW inventory (300)
2. Enter Order
3. Update DB inventory (240)
E N D

130

240

Order: Database textbook
70 copies

DB DW

Order: Database textbook
60 copies

200

300

BEGIN
1. Read DB inventory (200)
2. Enter Order
3. Update DB inventory (130)
END

BEGIN
1. Read DB inventory (200)
2. Enter Order
3. Update DB inventory (140)
END

130

140

300

Figure 15-8 Concurrent transactions.

time and start executing. Within microseconds, a second transaction, T2, may start
execution from another place and interact with the database. Transaction T2, with
fewer reads and updates, may finish sooner, and transaction T1, although started
earlier, may end later. These are concurrent transactions or transactions that inter-
act with the database simultaneously. Transaction T1 may read and update a par-
ticular record in the database, while T2 may read and update an altogether different
record. On the other hand, both may need to read and update the same record.
Figure 15-8 presents these two cases: In the first case, T1 reads and updates the
inventory record for database textbook and T2 reads and updates the inventory