100 likes | 257 Views
Session 3959 Using Database Triggers in Unidata Marriott Ballroom Salon II Monday 3/12/2007 2:00pm Jeff Butera Hampshire College. Outline. What is a trigger? What can triggers do? How do I setup a trigger? How do I use them in production? Questions?. What is a trigger?.
E N D
Session 3959 Using Database Triggers in Unidata Marriott Ballroom Salon II Monday 3/12/2007 2:00pm Jeff Butera Hampshire College
Outline • What is a trigger? • What can triggers do? • How do I setup a trigger? • How do I use them in production? • Questions?
What is a trigger? • Piece of executed code • Triggered by certain events Unidata specifics: • Code is a UniBasic subroutine or function • Trigger can be invoked when a record is • Updated (written) • Delete
What can triggers do? • Triggers are useful for automated processing • ADDOPR, CHGOPR (?!) • Virtual fields too slow? • No problem – make ‘em computed with a trigger • Stored computed columns • Status and/or status date fields • Notify (email?) when certain things happen...
How do I setup a trigger? • Create subroutine with 5 arguments: EXECSTAT (0,1,2) DICT.FLAG (‘’, ‘DICT’) FILE (‘STUDENTS’) ID (‘0023450’) RECORD • Compile and GLOBALLY catalog • CREATE.TRIGGER FILENAME SUBNAME UPDATE
How do I use these for real? • Hampshire has truckloads of custom code/files • On ALL custom files, I use triggers for ADD/CHG OPER, DATE, TIME Why not Envision? • We use triggers on some Datatel files too! • We use WORKORDERS for Phys Plant and IT • PPWP (Web Params) only allows single Plant Type and single email address for web work orders • No problem – worked around with a trigger
SUBROUTINE H08.UT.H08.CR.EVENTS(X.EXEC,X.DICT,X.FILE,X.ID,X.REC) X.EXEC=2 IF X.DICT=’’ THEN X.REC<4>=UPCASE(@LOGNAME) X.REC<5>=DATE() X.REC<6>=TIME() IF X.REC<1>=’’ THEN X.REC<1>=X.REC<4> X.REC<2>=X.REC<5> X.REC<3>=X.REC<6> END END RETURN :BASIC CUSTOM.SOURCE H08.UT.H08.CR.EVENTS :CATALOG CUSTOM.SOURCE H08.UT.H08.CR.EVENTS FORCE :CREATE.TRIGGER H08.CR.EVENTS H08.UT.H08.CR.EVENTS UPDATE
Non-trivial Examples • Search for HTML tags in free-form text • Strip bad (unprintable) ASCII from free-form text • Correct WORKORDER plant/email • Set billed flag for parking stickers • Detect data irregularities with evaluations (text types) • FMLA check for approved timecards Why not place this code in screens/batch processes?
Need More Information? • Unidata Documentation 6.1 (a little thin): • Developing Unibasic Applications • Chapter 4 – Maintaining Data in Files • Database triggers pp. 4-6
Questions? Jeff Butera jeff@hampshire.edu http://jeff.hampshire.edu