Using SQL Triggers

If you are using software based on SQL (Standard Query Language), such as Microsoft SQL, you have the ability to add triggers.  A trigger is an event that is executed (triggered), when records are added, deleted, or changed, in the SQL database.  There are many uses for triggers.

One that I did recently, puts records into a table, that is used by a secondary processing system that the customer has.  When the sale ticket uses specific pay codes, some of the data needs to be captured, to be used by software that is specific to part of their business.  By using a trigger on the tables used by the point of sale, this special table is immediately updated with the information needed by the second, specialized, application.  In other words, the second application has immediate access to the sale data, without the need to search through the information on many sales tickets that are not applicable.  The performance impact at the time the sale is made, is so minimal that it is not noticeable.  The improved performance eliminating the search in the second application is significant.

That is the beauty of a SQL trigger.  The processing occurs automatically and, if properly designed, with no noticeable impact to the end user.

Almost any processing can be done in a trigger.  In the case above, the payment code is checked, and if it is one of those needed by the second application, the necessary information is captured to a different table, and the sale process then goes on as in all other sales.  

Other application of SQL triggers that we have implemented, include re-calculating sales tax rates based on the sale location (a requirement in Washington state, among others), and automatically updating the sale price for repeat customers.  There are any number of possible uses for SQL triggers.

While just about any processing can be performed in a trigger, the trigger must be properly designed so that it does not adversely impact the flow of business.  For example, in a point of sale system, you would not want to perform processing that could take several minutes.  Your customers do not want to wait those several minutes to get their receipt.  You could, however, flag the records for the additional processing, and have another procedure that performs the actual additional processing.  This additional processing could be made to happen automatically (which will be addressed in a future blog). 

Contact CCS Retail Technical Support for Custom CounterPoint SQL and Passport Business Systems SQL programming.