Neil Main

The requirement for a 'trigger framework' (TF) arose out of developing a new database system to manage data associated with the population of a region for the local governing bodies. After designing the database structure, there was no clear definition of the triggers that were going to be required, and the design of the database lent itself that all tables requiring certain data to be managed on the insert, update, and deletion of a table's rows. Also, from prevision experience, the firing of triggers in a specific order would likely be a future requirement.

The TF builds the trigger code for each table based on configuration data that defines what actions will require implementation. When a trigger is fired, it passes control to the central TF package, which houses all the code developed for the framework. The framework reads the configuration data for a table that has fired the trigger and implements the associated instructions for that table. The framework is also designed to handle 'mutating' table errors, so it had the ability for a table to implement data actions upon itself. This was a core requirement due to the design of the database. The TF was fully implemented using PL/SQL.

The system handles all aspects of a veterinary practice associated with small animals such as cats, dogs, and rabbits. The initial system consisted of four main sections; clinical, client, financial, and appointment data. It was further enhanced to cover reminder notifications and a customized practice-based animal insurance scheme. The system was designed in the latter half of 1998 and went live in January 1999. It was used until December 2018. The system was operated over a LAN with nine terminals and a VPN for remote access.

It had two receipt/label printers for production drug labels and client receipts. Data was integrated with drug supplier's databases, and to insurance, which produced the updated files required, and uploaded to the practice's bank to set up and manage client direct debit payments.

The system was developed using MS Access and consisted of two databases; the core database and the client database. The core database held just the data and client database linked to it and all the screen forms, coding, and reporting. The system was fully menu-driven, with security controlling user access to the data settings.

This retail system with POS was required to manage a warehouse a number of shops with stock rooms and an e-shop element. It was developed around a central PostgreSQL database implemented over a VPN. The core system consisted of two parts; stock control and sales based around a POS system.

The stock control handled all functions relating to inventory control, so the stock could be tracked from placing a purchase order through to its final sale in one of the shops. The stock had to be fully coded, providing management reporting through a simple structured interface to enabling complex reports to be generated. The sales section had to be simple to operate, covering all aspects relating to sales activity for the operators. This meant that a barcoding touch screen was required, which had an influence on the POS screen layout. The POS had to link into a government system to register all sales transactions.

The POS system was developed using C#. Management reporting was developed in MS Access using ODBC to link into the PostgreSQL database. Using these two development environments, a fully operational system was developed in record time.