SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
2-139
Examples of CREATE TRIGGER
This behavior is different from a row trigger because each value in the rowset might
match multiple rows in the subject table. Therefore, multiple rows might be affected
(updated or deleted) before the action of the trigger is executed.
Contrast this behavior with row triggers where the trigger action is executed once for
each affected row.
For INSERT statement that use rowsets, an INSERT statement trigger is triggered
once for the entire rowset.
Examples of CREATE TRIGGER
Before and After Triggers
Suppose that you have a database to record patients’ vital signs and drugs prescribed
for them. The database consists of these tables:

vital_signs, which records vital signs at each visit

prescription, which records prescriptions written for each patient

generic_drugs, which lists generic drug equivalents for brand-name drugs
The prescription table is created like this:
CREATE TABLE prescription
( id INTEGER NOT NULL
NOT DROPPABLE,
pat_id INTEGER NOT NULL,
issuing_phys_id INTEGER NOT NULL,
date_prescribed DATE DEFAULT NULL,
drug VARCHAR(80) DEFAULT NULL,
record_id INTEGER NOT NULL,
dosage VARCHAR(30) NOT NULL,
frequency VARCHAR(30) DEFAULT NULL,
refills_remaining INTEGER DEFAULT NULL,
instructions VARCHAR(255) DEFAULT NULL,
primary key (id))
STORE BY PRIMARY KEY
ATTRIBUTES EXTENT (1024,1024) MAXEXTENTS 700
LOCATION $D00001.ZSDDEMO1.PRSCR000;
You can create a BEFORE trigger on prescription so that when a prescription is
entered, if the prescribed drug is found in generic_drugs, a generic drug is
substituted for the brand-name drug, and the instructions for the drugs are updated:
CREATE TRIGGER alternate_drug
BEFORE INSERT ON prescription
REFERENCING NEW AS newdrug
FOR EACH ROW
WHEN (upshift(newdrug.drug) IN
(SELECT upshift(generic_drugs.drug) FROM generic_drugs))
SET newdrug.drug = (SELECT
upshift(generic_drugs.alternate_drug)