SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-103
Examples of CREATE TRIGGER
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)
FROM generic_drugs
WHERE upshift(newdrug.drug) =
upshift(generic_drugs.drug))
,newdrug.instructions = newdrug.instructions ||
' Prescribed drug changes to alternative drug.';
You can create an AFTER trigger on vital_signs so that when that table is updated,
NonStop SQL/MX checks the patient’s weight and height. Based on their values, this
trigger might add a record to prescription to create a new prescription for a weight
loss drug with instructions that indicate that this is a free sample:
CREATE TRIGGER free_sample
AFTER INSERT ON vital_signs
REFERENCING NEW AS sample
FOR EACH ROW
WHEN (sample.weight > 299 and sample.height < 69)
INSERT INTO prescription
(id, pat_id, issuing_phys_id, record_id, date_prescribed,
drug, dosage,
frequency, refills_remaining, instructions)
VALUES
((SELECT sequence + 1 from prescription_seq),
(SELECT pat_id FROM record WHERE sample.id =
record.vital_id),
(SELECT phys_id FROM record WHERE sample.id =
record.vital_id),
(SELECT record.id FROM record WHERE sample.id =
record.vital_id),
CURRENT_DATE, 'POUND OFF', '200 mg', '1 pill 1 hour before
each meal', 0, 'Free sample no refills'
);
Rowsets and Triggers
Suppose that you have a table with this rowset definition:
Rowset[10] short ArrayA;