FUNCTIONAL DESCRIPTION of CONSOLIDATED TRIGGERS
The consolidated functional trigger
descriptions are
CUSTOMER INSERT
Rule 1: Raise an application error
if the following is false: ACCOUNT_TYPE and ACCOUNT_ID are either
both NULL or both NOT NULL. This can be achieved from evaluating
the correlation values in the row trigger.
Rule 2: If the ACCOUNT_TYPE is
"PR," then recursively INSERT an "RE" account
for the same CUST_NAME. This recursive INSERT
must be done during after-statement trigger processing. We must
use a flag which blocks the INSERT trigger logic from executing
during the recursive INSERT.
CUSTOMER UPDATE
Rule 1: Raise an application error
if the following is false: ACCOUNT_TYPE and ACCOUNT_ID are either
both NULL or both NOT NULL. This can be achieved from evaluating
the correlation values in the row trigger.
Rule 3: If ACCOUNT_ID changes from
NULL to NOT NULL, then decrement (retaining a positive value)
the PURCHAASE_PRICE of any PURCHASE_ORDER for this customer. The
update to PURCHASE_ORDER must be done in after-statement processing
because PURCHASE_ORDER is a child to CUSTOMER. The row-level trigger
must save the primary key of the update in a
PL/SQL table.
CUSTOMER DELETE
Rule 4: This trigger depends upon
the EMP_SERVICE trigger to populate a PL/SQL
table with EMP_ID/CUST_ID records where SALES > 5000. It also
depends upon a PURCHASE_ORDER trigger to populate a PL/SQL
table with the PURCHASE_DATE for any date past 30 days. The after-statement
trigger processing scans both tables to determine a constraint
violation.
EMP_SERVICE_LOG DELETE
Rule 4: Raise an application error
if SALES from EMP_SERVICE SALES exceeds $5000, and there exists
a PURCHASE_DATE on a PURCHASE_ORDER for this customer which is
past 30 days. This trigger must be able to detect a delete cascade
from CUSTOMER or EMP_SERVICE. This trigger performs no operations
if the delete is the result of a delete cascade. This logic can
be enforced at the row level by evaluating the SALES amount from
the parent EMP_SERVICE table and the PURCHASE_DATE from PURCHASE_ORDER
table.
EMP_SERVICE DELETE
Rule 4: Raise an application error
if the delete violates the constraint on the SALES amount and
PURCHSE_DATE from PURCHASE_ORDER. The trigger must be able to
detect a delete cascade. If not in a delete cascade, the trigger
reads the PURCHASE_DATE from PURCHASE_ORDER for the row being
deleted. If the constraint is violated an error is raised. If
in a delete cascade, for any record where SALES > 5000, the
row-level trigger stores the concatenated primary key in a PL/SQL
table - it does not initialize the PL/SQL
table and it does not enforce the rule in after-statement processing.
PURCHASE_ORDER INSERT
Rule 5: Raise an application error
if WIDGET_ID references a WIDGET with STATUS = "on"
and SAMPLE = "yes." This can be implemented with a
row-level trigger since an INSERT into a child can read a parent.
Rule 6: If the CUSTOMER's STATE
is "AK" or "HI" and QUANTITY < 100, then
increment the PURCHASE_PRICE. This can be implemented at the
row-level by reading the parent table CUSTIOMER for the STATE
attribute and incrementing the PURCHASE_PRICE. This must be done
is a BEFORE insert-row trigger since it modifies a correlation
value.
PURCHASE_ORDER UPDATE
Rule 6: If the QUANTITY is changed
to be greater than 100 or less than 100, then increment or decrement
the PURCHASE_PRICE if the CUSTOMER's STATE is "AK" or
"HI." This trigger fires only if QUANTITY is being
updated. This constraint is enforced at the row level since it
reads from the parent CUSTOMER table.
PURCHASE_ORDER DELETE
Rule 4: If the DELETE is the result
of a delete cascade from CUSTOMER, the trigger stores the CUST_ID
and the primary key in a PL/SQL
table for any date past 30 days. This table is constructed for
the CUSTOMER DELETE trigger enforcement of rule 4. This affects
the row-level trigger only. The PL/SQL
table initialization is executed by the CUSTOMER table.
Rule 7: Raise an application error
if the PURCHASE_DATE is the current date, the widget STATUS =
"on" and the customer has a NOT NULL ACCOUNT_ID. We
ignore the enforcement if the delete is the result of a delete
cascade. This constraint check is performed in row-level processing.
Since we ignore enforcement during DELETE CASCADE, any mutating
table error is captured and ignored.
WIDGET UPDATE
Rule 5: Raise an application error
if the WIDGET is part of any PURCHASE_ORDER and the widget has
the attributes: STATUS = "on" and SAMPLE = "yes."
Since this requires reading the child PURCHASE_ORDER table, the
rows being updated must be saved in a PL/SQL
table. The after-statement processing will check for any occurrence
of these widgets in PURCHASE_ORDER.