Transcript Database Triggers

Constraints
Review
What is a constraint?
• Unique – forbids duplicate values
• Referencial – Foreign key
• Check Constraint – sets restrictions on
data added to a specific table
SQL vs ADDPFCST
Database Triggers
Trigger?
• A condition that causes some procedure to
be executed.
• On the Iseries, a program that is called
when a program tries to insert, update or
delete a database record
• A trigger can be more complex than a
check constraint
Writing Trigger Programs
• Just as you do any HLL program
• AddPFTrg (Add Physical File Trigger) to
associate the program with the database
file.
6 Trigger Conditions
•
•
•
•
•
•
Before Insert
Before Update
Before Delete
After Insert
After Update
After Delete
Advantages
• Be sure that the actions coded in the
trigger program are executed irregardless
of the application that is trying to modify
the database
Disadvantages
• You must be aware that triggers exist
RmvPfTrg
• Removes a trigger program from a
database
Coding a Trigger Program
• Can be coded in any iSeries HLL
• Must have 2 parameters that conform to
IBM-defined interface for trigger programs
– Trigger buffer
• Before and after image of the affected row
• See Fig 16.1 in text book
– Trigger length
• Length of the buffer
Trigger Program
• Trigger Programs must cause an
exception message.
• Best way to do this is to send a program
message to the calling program.
• This causes the application to crash and
the database update is halted.
Vendor Master
CREATE TABLE VENDOR
(VENID DEC (7 , 0) NOT NULL WITH
DEFAULT,
NAME CHAR (30 ) NOT NULL WITH
DEFAULT,
MINORD DEC (7 , 0) NOT NULL WITH
DEFAULT,
CONSTRAINT VENDORPK PRIMARY
KEY (VENID))
Write a Trigger Program
• Minimum order must be greater than
100.00
Student Master
CREATE TABLE STUDENT
(STUDID NUMERIC (9 , 0),
SNAME CHAR (30 ),
PROV CHAR (15),
ACTBAL NUMERIC(11, 2))
Write a trigger program that will
not allow records to be updated
where the PROV = ON and the
ACTBAL < 100