Neoview SQL Reference Manual (R2.2)

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
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));
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, SQL
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 =
CREATE TRIGGER Statement 85