SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-108
Examples of CREATE TRIGGER
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);
If trigger trg1 is defined as an insert statement trigger on tab1,and trg2 is defined
as an insert row trigger on tab1, when the DML statement is executed, the two
triggers are fired. The action of trg1 executes once for the entire statement, while
trg2 executes ten times, once for each element in the rowset.