Triggers represent an elegant method to integrate own functions but also changes to processes into the ERP system EULANDA®.
What are triggers
Triggers are a special form of stored procedures. They can contain almost any SQL commands and are automatically called by the SQL server during certain database operations (insertion, modification, deletion of data records).
The possibility to display error messages in the trigger, which are displayed to the end user and the possibility to completely undo the original database operations that triggered the trigger (rollback), opens up a wide range of application possibilities.
Some of the possible tasks for a trigger are:
- Check changed data for consistency. And, if necessary, the output of error messages and the undoing of changes.
- Maintenance of dependent data
- Deletion of data no longer required
- Form totals in superordinate tables. For example, changing the order items automatically maintains the total price in the order header.
- Logging of changes, e.g. for monitoring individual users, etc.
- but also completely different actions, such as automatically sending an e-mail when converting an order into a delivery note.
Triggers are assigned to individual tables. For each trigger, the operations for which it is to be triggered are defined. These operations are:
INSERT, UPDATE and DELETE
A single trigger can also monitor several of these operations simultaneously. However, the same trigger cannot be stored for several tables, even if the table columns used are the same.
EULANDA® already defines its own triggers for almost all tables, which map the business logic. However, Microsoft SQL-Server® has the pleasant feature of storing any number of triggers per table. There are no limits for developers in terms of extensibility in this respect.
To integrate user triggers seamlessly into EULANDA® and not lead to malfunctions, however, some things have to be considered.
Rules for creating user triggers
- The trigger name must comply with the naming conventions for SQL objects. The name must begin with TR_USER_, followed by the ID of the relevant table and the ID of the triggering table operation.
For more information, see Naming Conventions.
- The first command in the trigger must be SET NOCOUNT ON. Otherwise, the database interface OLE/DB or ADO can no longer assign the changes correctly. This can lead to unforeseeable errors in EULANDA®.
- Do not execute any SELECT commands or PRINT commands that return data or information to the application level. This also leads to malfunctions in EULANDA®. SELECT commands that only assign values to local variables are allowed.
- The actual database operation is not completed until all triggers have been processed. The trigger code therefore has a considerable influence on the performance of the overall system. Make sure that time-consuming commands within a trigger are not executed unnecessarily often. This can be prevented by functions such as UPDATE(). This problem will be discussed in more detail later.
- If a ROLLBACK is performed in the trigger, RAISERROR must be triggered with a meaningful error message and a severity level of 16. Otherwise EULANDA® assumes that no error has occurred.
- triggers must take into account the fact that the database operation that triggered the trigger also affects more than one or possibly no rows in the table. Accordingly, the pseudo tables inserted and deleted have varying line numbers (see example C:)
Example of a user trigger
CREATE TRIGGER TR_USER_AF_INSUPD_StatusPruefung ON Auftrag
FOR INSERT, UPDATE
AS /* This must be the first command in a trigger */ SET NOCOUNT ON /* This is where the actual trigger commands begin*/
Keep server load low
Check the changed fields with UPDATE()
With very few exceptions, you only want to monitor a limited number of columns in a trigger. Transact-SQL provides the UPDATE(column name) function to determine the changed columns. Always enclose your trigger commands in a condition block that contains all desired fields:
IF UPDATE(Status) OR UPDATE(Objekt) OR UPDATE (Datum)
BEGIN /* Your trigger commands */ END
The UPDATE function always returns False in DELETE triggers.
Optimized SELECTS and UPDATES
Minimize or optimize SELECT or INSERT commands. If necessary, create indexes in the corresponding tables to shorten the execution time.
Just because the function UPDATE(column name) returns True, the column does not have to have been changed. For example, the command
UPDATE Adresse SET RabattGr = 'A' WHERE RabattGr = 'A'
does not address the discount group, but UPDATE does. If an elaborate SQL command depends on a"real" change to the column, you should provide for further checks. For example, the following command creates a temporary table with the address IDs that were actually changed:
DECLARE @t TABLE (id int) INSERT @t SELECT i.id FROM inserted i, deleted d WHERE i.id = d.id AND i.RabattGr <> d.RabattGr
A: Checking Changes and Issuing an Error Message with Rollback
In this example, when the order items are changed, the system checks whether there is still a positive revenue. If this is not the case, an error message is displayed and the change is discarded (note: this check could also be performed more efficiently with a CHECK-CONSTRAINT).
CREATE TRIGGER TR_USER_AFP_INSUPD_Ertrag ON AuftragPos FOR INSERT, UPDATE AS SET NOCOUNT ON IF ( UPDATE(VkRab) OR UPDATE(Menge) OR UPDATE(PreisEH) ) BEGIN IF EXISTS (SELECT * FROM inserted WHERE (Menge > 0) AND (Ertrag < 0) ) BEGIN RAISERROR('[VENDOR:USER][ADRESS:USER]Die Marge zu gering ist!', 16,1) ROLLBACK END END
B: Creating a log entry in the SQL server
This trigger logs changes to the date or order status of the order. Only orders older than one hour are taken into account. The information is recorded in the SQL server log. This can be viewed as a text file in the SQL Enterprise Manager or in Windows Explorer.
CREATE TRIGGER TR_USER_AF_UPD_Datum ON Auftrag FOR UPDATE AS SET NOCOUNT ON IF UPDATE(Datum) OR UPDATE(BestellStatus) BEGIN /* Check whether orders exist that are older than a ** hour is */ IF EXISTS(SELECT * FROM inserted WHERE DATEDIFF(hour,CreateDate,GETDATE())>1 ) BEGIN /* Declaration of the variables used */ DECLARE @userid int, @nr int, @count int /* Determination of the order number and the number of ** changed orders. If several requests are affected ** In this example, only the smallest number is determined. */ SELECT @nr = MIN(KopfNummer), @count = COUNT(*) FROM inserted /* Create an entry in table cnProesses ** with all information about the user and workstation ** on which the command is executed */ EXEC cn_UserId @userid OUT /* Entry of an error message in the SQL server log ** the severity level is 1. ** not passed on to EULANDA */ RAISERROR('Date/OrderStatus in %d order item(s) changed in (Order#%d) of process %d (see table cnProcesses for details)', 1,1, @count, @nr, @userid) WITH LOG END END
C: An update command that does not change any lines
The following SQL script shows that an UPDATE command does not necessarily have to change rows. For trigger programmers, this means that the case of zero lines in the pseudo tables must also be taken into account:
CREATE TRIGGER TR_USER_AR_UPD_CountTest ON Artikel FOR UPDATE AS /* ** This trigger is ONLY used to clarify the values specified in the
** Text described problem.
** Otherwise this is NOT a valid trigger for the use of
** with the EULANDA® merchandise management! */ IF UPDATE(Barcode) SELECT COUNT(*) [Affected rows with barcode changes] FROM inserted ELSE SELECT COUNT(*) [No barcode changes] FROM inserted GO UPDATE Artikel SET Barcode = '4711' WHERE 1 = 2 GO DROP TRIGGER TR_USER_AR_UPD_CountTest
RAISERROR Creating User Indexes
Creating User Check Constraints