BASIS DBMS: Triggers and Stored Procedures

1. BASIS DBMS:Triggers and Stored Procedures Dr. Kevin King,CIO Nick Decker,Engineering Supervisor BASIS International Ltd.

2. Overview • Triggers • Automatically run code when reading or writing • Configurable to run before, instead, or after • Stored Procedures • Consolidated business and data logic • Facilitates TP access while maintaining DB integrity • Store data logic in DB instead of application • Simplified Web service (RPC)

3. BASIS DBMS – Triggers • Trigger Overview • A trigger is a BBj program that automatically executes in response to access to a file • Triggers are available for three types of file access • Reads • Writes • Removes • Triggers fire regardless of the file access method since they are implemented at the filesystem level • READ RECORD • SQL Select • RecordSets

4. BASIS DBMS – Triggers • Trigger Overview • Three types of triggers • Before • “ before read,” “ before write,” “ before remove” • Executes before the read takes place • After • “ after read,” “ after write,” “ after remove” • Executes after the read takes place

5. BASIS DBMS – Triggers • Trigger Overview • Three types of triggers (continued) • Instead Of • Takes the place of, rather than supplements, the normal file activity • Is mutually exclusive of a Before/After trigger • Will not perform the actual Read/Write/Remove – it is up to the trigger code to do whatever action is appropriate

6. BASIS DBMS – Triggers • Trigger Overview • Triggers are file-based • Triggers are tied to specific tables and actions • Each file can have its own set of triggers • Triggers can be ‘shared’ • Multiple triggers can reference the same program • Trigger programs can CALL other programs

7. BASIS DBMS – Triggers • Trigger Overview • Triggers are managed from the Enterprise Manager • Separate mounted directories for organization • Assign programs to run for each of the nine triggers per file • Enable/Disable triggers • Per file • Per trigger • Preview trigger code

8. BASIS DBMS – Triggers

9. BASIS DBMS – Triggers • Trigger Overview • Triggers have two components • Trigger definition file • XML definition file • Located in the same directory as the file that the trigger definition file defines • Maintained automatically by the EM • Contains a reference to the BBj program for each type of trigger • BBj source program that runs when the trigger fires

10. BASIS DBMS – Triggers • Trigger Overview • Data file, trigger definition file, and BBj program may all be stored in the same directory • Easier to maintain • Easier to deploy • Trigger definition file may optionally reference BBj programs in other directories • BBj programs may be SAVEP’d for added security

11. BASIS DBMS – Triggers • Trigger Uses • Log access to a file • Audit trail logging all reads/writes/removes

12. BASIS DBMS – Triggers

13. BASIS DBMS – Triggers • Trigger Uses • Restrict access to data • Only allow particular users to read • Tables • Records • Fields in a record

14. BASIS DBMS – Triggers • Trigger Uses • React to an event • Keep a third party database in synch by inserting/updating/removing records at the same time instead of doing lengthy bulk copies • Takes much less time • Only updates the records that have changed • Do not have to constantly poll to see if the data has changed

15. BASIS DBMS – Triggers • Trigger Uses • Enforce database integrity • Validate data against related tables before inserting into the table • Ensure related tables stay in synch with one another • When removing a record from the OrderHeader table, use a trigger to remove all OrderLine entries for that order number • When removing the last OrderLine, use a trigger to remove the corresponding order number in the OrderHeader table

16. BASIS DBMS – Triggers • Trigger Uses • Manipulate data without changing the application • Modify the data in an After Read trigger, so the client application will receive a modified record rather than what was in the file • Modify the data in a Before Write trigger, so the updated record is written to the file • Normalize data • Unpack or convert dates • Encrypt fields

17. BASIS DBMS – Triggers

18. BASIS DBMS – Triggers

19. BASIS DBMS – Triggers

20. BASIS DBMS – Triggers

21. BASIS DBMS – Triggers

22. BASIS DBMS – Triggers

23. BASIS DBMS – Triggers

24. BASIS DBMS – Triggers

25. BASIS DBMS – Triggers • Trigger Uses • “Instead Of” triggers accomplish different goals • Write data to a different backend database or file • Flag a record as inactive or move it to a backup table instead of processing the remove

26. BASIS DBMS – Triggers • Trigger Uses Example: Flag a record as inactive or move it to a backup table instead of processing the remove chan = unt key$ = "000001" open(chan)"CUSTOMER" remove(chan,key=key$) print "Removed Cust_Num: ", key$ close(chan)

27. BASIS DBMS – Triggers

28. BASIS DBMS – Triggers

29. BASIS DBMS – Triggers

30. BASIS DBMS – Triggers • Trigger Uses • BBj programs provide maximum flexibility • CALL existing libraries of code • Launch external applications • Send an e-mail when a file changes • Log transaction • Execute Java code

31. BASIS DBMS – Triggers • Trigger Overhead • Everything occurs at the filesystem level • Trigger checks are fast with little overhead • Triggers validated when connection made to the file • An OPEN() does not succeed unless all active defined triggers are available • Trigger interpreters cached to minimize overhead • Active triggers incur some overhead • Complexity of code • Quantity of code

32. BASIS DBMS – Triggers • Trigger Notes • Error trapping is imperative • Similar to BACKGROUND programs, I/O is not available and not desired for deployment • Errors may be written to a log • Recursion possible • If a Before Read trigger causes another read on the same table, it can get into an infinite loop

33. BASIS DBMS – Triggers • Trigger Notes • Debugging is more challenging • I/O and error reporting is limited to logging in a deployment paradigm • MSGBOXes can be used for limited interaction during debugging sessions • Triggers can create complex interdependencies between multiple programs and tables

34. BASIS DBMS – Triggers • Trigger Notes • Debugging is more challenging (continued) • Unanticipated and unexpected behavior may occur when using triggers that interact with one another • Untrapped errors in the trigger are passed on to the event that caused the trigger to fire • If the trigger code dies with an untrapped !ERROR=12 on an OPEN(), the corresponding READ RECORD in the client application will result in a very unusual !ERROR=12

35. BASIS DBMS – Stored Procedures • Stored Procedure Overview • Associated with a database • Provides an API mechanism • SQL compliant applications can interface with the Stored Procedures • Applications interact with the Stored Procedure rather than connecting directly to the underlying tables in the database

36. BASIS DBMS – Stored Procedures • Stored Procedure Overview • Often referred to as SPROC, pronounced “sprock” like rock or “ess-prock” for those ‘in the know’ (or those tired of saying Stored Procedure) • Many DBMSs implement SPROCs as a way to provide advanced language capabilities beyond traditional SQL • Control flow IF/THEN/ELSE, SWITCH/SWEND • Looping structures FOR/NEXT, WHILE/WEND • Advanced error handling

37. BASIS DBMS – Stored Procedures • Stored Procedure Overview • As with Triggers, SPROCs are really BBj programs designed for a special purpose • Benefits of using BBj Programs include • Use existing programs • Call other BBj programs • Extremely powerful and flexible syntax • Entire language is available • User-defined functions • SCALLs • Java code • SQL

38. BASIS DBMS – Stored Procedures • Stored Procedure Overview • Benefits (continued) • Easy to integrate into existing systems • Easy to write and maintain • SAVEP SPROCs for added security • Third party applications have standardized access to BBj code, functions, libraries, etc. • SPROC is similar to a public function available to any SQL capable application

39. BASIS DBMS – Stored Procedures • Stored Procedure Overview • SPROCs - database-centric • Defined on a per database level in the Enterprise Manager • Definition and specification defined in the Data Dictionary • Applications interface with SPROCs via SQL

40. BASIS DBMS – Stored Procedures • Stored Procedure Overview • SPROCs have two components • SPROC definition • Stored in the Data Dictionary • Maintained from the Enterprise Manager • BBj program • Located anywhere • Interact with other programs

41. BASIS DBMS – Stored Procedures • Stored Procedure Overview • SPROCs similar to Triggers, but different • Explicitly called, whereas a trigger occurs automatically in response to an event • Not specific to any one table • Defined on a database level rather than a file-level • SQL-specific • Available to all SQL-capable languages and applications • BBj, VPRO/5 • C++, Perl, Visual Basic • ODBC/JDBC • Microsoft Office, OpenOffice.org

42. BASIS DBMS – Stored Procedures • Stored Procedure Overview • Stored Procedure Benefits • Keep business logic in one place instead of inside each client application • Easier to maintain • Efficient reuse of existing code and libraries • Lower development costs • Easier integration with new orthirdparty applications • Business logicdoes nothave to be added to applications, regardless of whether they are existing BASISprogramsorthirdpartyapplications • Improved application reliability • Business logic separated from the application

43. BASIS DBMS – Stored Procedures • Stored Procedure Overview • Stored Procedure Benefits (continued) • Performance increases for third party applications • Processing of the data performed server-side by BBj • Only pertinent results sent back ‘over the wire’ to the client application • Application does not have to request all of the data, sift through it, and figure out what is pertinent • Application does not have to make multiple requests back to the server to get/set information in other tables

44. BASIS DBMS – Stored Procedures • Stored Procedure Specifics • Return value allows the SPROC to send a single piece of information to the client application • Optional • Only one available • Typically used as a status code • Return success/failure of the SPROC

45. BASIS DBMS – Stored Procedures • Stored Procedure Specifics • Return Value (continued) • Can be of any SQL type • CHAR • VARCHAR • LONGVARCHAR • INTEGER • NUMERIC • DATE • TIMESTAMP • TIME

46. BASIS DBMS – Stored Procedures • Stored Procedure Specifics • Parameters offer a mechanism for the client application and the SPROC to share data • Gives flexible way to use a single SPROC for many different scenarios • Application could provide filtering information for queries • Application could provide different instructions, so the SPROC will operate on different tables • Makes SPROCs more efficient • The same SPROC can run multiple tasks, dependant on the input from the client application

47. BASIS DBMS – Stored Procedures • Stored Procedure Specifics • Parameters (continued) • Parameters are optional • An SPROC may have no parameters • Example: An SPROC that is designed to synchronize tables which only has a return value • An SPROC may have several parameters • Flexible interaction with the client application

48. BASIS DBMS – Stored Procedures • Stored Procedure Specifics • Parameters (continued) • Parameters can be of any SQL type: • CHAR • VARCHAR • LONGVARCHAR • INTEGER • NUMERIC • DATE • TIMESTAMP • TIME

49. BASIS DBMS – Stored Procedures • Stored Procedure Specifics • Parameters (continued) • Parameters are directional • IN • Applications sends data into the SPROC via a parameter • OUT • SPROC sends data back to the application via a parameter • IN/OUT • Application sends data to the SPROC and the SPROC sends data back to the application via a parameter

50. BASIS DBMS – Stored Procedures • Stored Procedure Specifics • Result Sets allow SPROCs to return a table or set of data to the client application • Similar to the client executing a SELECT, except that the SPROC is in charge of determining the structure of the result set • Client application executes the SPROC instead of the usual SELECT, so the SPROC’s code and underlying logic is transparent to the application