1. Constraints Review

2. What is a constraint? • Unique – forbids duplicate values • Referencial – Foreign key • Check Constraint – sets restrictions on data added to a specific table

3. SQL vs ADDPFCST

4. Database Triggers

5. 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

6. Writing Trigger Programs • Just as you do any HLL program • AddPFTrg (Add Physical File Trigger) to associate the program with the database file.

7. 6 Trigger Conditions • Before Insert • Before Update • Before Delete • After Insert • After Update • After Delete

8. Advantages • Be sure that the actions coded in the trigger program are executed irregardless of the application that is trying to modify the database

9. Disadvantages • You must be aware that triggers exist

10. RmvPfTrg • Removes a trigger program from a database

11. 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

12. 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.

13. 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))

14. Write a Trigger Program • Minimum order must be greater than 100.00

15. Student Master CREATE TABLE STUDENT (STUDID NUMERIC (9 , 0), SNAME CHAR (30 ), PROV CHAR (15), ACTBAL NUMERIC(11, 2))

16. Write a trigger program that will not allow records to be updated where the PROV = ON and the ACTBAL < 100