SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-151
Examples of CREATE TRIGGER
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)
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),