SQL/MX 3.x Reference Manual (H06.22+, J06.11+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—640322-001
2-109
Examples of CREATE TRIGGER
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;
This embedded DML statement inserts ten rows into table tab1.
EXEC SQL insert into cat.sch.tab1 values (:ArrayA);