1 / 10

Session 3959 Using Database Triggers in Unidata Marriott Ballroom Salon II Monday 3/12/2007 2:00pm

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

april
Download Presentation

Session 3959 Using Database Triggers in Unidata Marriott Ballroom Salon II Monday 3/12/2007 2:00pm

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Session 3959 Using Database Triggers in Unidata Marriott Ballroom Salon II Monday 3/12/2007 2:00pm Jeff Butera Hampshire College

  2. Outline • What is a trigger? • What can triggers do? • How do I setup a trigger? • How do I use them in production? • Questions?

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

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

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

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

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

  8. 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?

  9. Need More Information? • Unidata Documentation 6.1 (a little thin): • Developing Unibasic Applications • Chapter 4 – Maintaining Data in Files • Database triggers pp. 4-6

  10. Questions? Jeff Butera jeff@hampshire.edu http://jeff.hampshire.edu

More Related