User Trigger (SQL API)

Trigger represent an elegant method, own functions and also changes at expirations, to integrate in the EULANDA® ERP Software.

Which are Trigger

Trigger are special stored procedures. They can contain arbitrary SQL commands and they are called automatically with certain database operations like (paste, change, delete of data records) by the SQL server.

The possibility to spend error messages in the trigger, which the final user gets indicated and the possibility to rollback the original data base operations those the trigger released. That opens a far spectrum of application possibilities.

Some to the possibility gave up for a trigger are:

Examination of changed data on their consistency. You could undo the changes if it is necessary.
Maintains from dependent data.
Deleting not any longer necessary data.
Education of hum in super ordinate tables. For example by change of the order items the total price in the order header is automatically maintained.
Log from changes e.g. for the monitoring of individual users etc..
In addition, completely different actions as for example the automatically dispatching of E-Mails with convert an order into a delivery note.

Trigger are assigned individual tables. And to each trigger fixed with which operations it to be triggered. These operations are:

INSERT, UPDATE und DELETE

The individual trigger can at the same time supervise also several of these operations. But the same trigger cannot be deposited with several tables, even if the used columns equal loud.

EULANDA® defines already to nearly all tables own trigger, which illustrate the business logic. Microsoft SQL-Server® has the pleasant characteristic as many as desired triggers per table to deposit. You as developers are thus in this regard none border in the expandability set.

So that user triggers smoothly integrate themselves into the EULANDA® and not to malfunctions lead you have to consider some rules.

Rules for put on user trigger

The trigger name must correspond with the named conventions of the SQL objects. The name must begin with TR_USER_ , followed of the contraction of the respective table and the contraction of the releasing table operation.
You find more information in the chapter of Name convention.
The first command of the trigger must be SET NOCOUNT ON. Otherwise the data base interface OLE/DB and ADO cannot assign the changes any longer correctly. This can lead to unforeseeable errors in EULANDA®.
No SELECT commands or PRINT commands may be implemented, which return data or information to the application level. This leads likewise to malfunctions in EULANDA®. SELECT commands, which assign values only to local variables are permitted.
The actual data base operation is only terminated, if all triggers will be processed. The trigger code has thus substantial influence on the performance of the overall system. It is to be certain that time intensive commands within a trigger are not unnecessarily often implemented. That could be prevented by the UPDATE() function. With this problem we deals later still more exactly.
If in the trigger a ROLLBACK is accomplished, it absolutely must before a RAISERROR with a meanigful error message and a severity level of 16 be released. Otherwise EULANDA® assumes no error arose.
Trigger must consider the fact, that the data base operation, those released the trigger also more than one concerns no lines in the table. Accordingly the pseudo tables inserted and deleted varying numbers of lines (see example C:)

Example of a user trigger:

CREATE TRIGGER TR_USER_AF_INS_StatusPruefung ON Auftrag
FOR INSERT, UPDATE
AS

/* This must be the first command in a trigger */
SET NOCOUNT ON

/* Here begin the actual trigger commands */

Server load keeps small

Examine the changed fields with UPDATE()

Up to very few exceptions you would only like a reduced number of columns to supervise in a trigger. Transact-SQL supplies with the function UPDATE (column name) the possibility to determine the changed columns. You have always to fit out the triggers commands in a condition block, which contains all desired fields:

IF UPDATE (Status) OR UPDATE (Object) OR UPDATE (Date)
BEGIN
  /* Your trigger commands */
END

Hint:
The UPDATE function in DELETE trigger returns always false.

Optimized SELECTS and UPDATES

Minimize and/or optimize the SELECT or INSERT commands. If it is necessary you put on indices in the appropriate tables, in order to shorten the run time.

Only because the function UPDATE (column name) supplies true, must the column not been changed. e.g. the command change:

UPDATE Address SET RabattGr = 'A' WHERE RabattGr = 'A'

not the group of discounts but UPDATE responds nevertheless. If a complex SQL command of a "genuine" change column depends, you should make further examinations. The following command produces for eample a temporary table with the address IDs which were really 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

Examples

A: Examination of changes and expenditure of an error message with rollback

In this example with changing the order position, it will be examined if a positive yield is present. If that is not the case, an error message is spent and the changes are rejected. (Hint: this examination could be accomplished also with an 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]The profit is less!', 16,1)
    ROLLBACK
  END
END

B: Creates a protocol entry in the SQL server

This trigger protocols changes of the date and/or order status of the order. Here only orders considered those are older than one hour. The information is held in the SQL server protocol. This can be seen in the SQL enterprise manager or in the Windows explorer as text file.

CREATE TRIGGER TR_USER_AF_UPD_Datum ON Auftrag
FOR UPDATE
AS

SET NOCOUNT ON

IF UPDATE(Date) OR UPDATE(OrderStatus) 
BEGIN
  /* Examine if orders exist, which are older as one hour */

IF EXISTS(SELECT * FROM inserted
    WHERE DATEDIFF(hour,CreateDate,GETDATE())>1 )
  BEGIN

    /* Declaration of the used variables */
    DECLARE @userid int, @nr int, @count int

    /* Determine the order number and the number of orders. 
    ** If several orders are concerned Falls 
    ** in this example only the smallest number is determined. */
    SELECT @nr = MIN(KopfNummer), @count = COUNT(*) FROM inserted

    /* Creates an entry in the table cnProesses
    ** with all indications of the user and workstation
    ** at that the instruction will be implemented */
    EXEC cn_UserId @userid OUT

    /* Entry of an error message into SQL server protocol
    ** The severity level is 1. thus the error is not passed on at EULANDA */

RAISERROR('Datum/BestellStatus in %d Auftragsposition(-en) 
      changed (Auftrag #%d) 
      ´from process %d (look table cnProcesses for more details)',
      1,1, @count, @nr, @userid) WITH LOG

  END
END

C: A update command which changes no lines

The following SQL script shows that an UPDATE command also must not change lines. That means for the trigger programmer, that they have to consider also the case of "null" lines in the pseudo tables:

CREATE TRIGGER TR_USER_AR_UPD_CountTest ON Article
FOR UPDATE
AS

/*
** The trigger serves ONLY for the elucidation the problem described in the text.
** This is otherwise NO valid trigger for the use 
** with the EULANDA® ware economic !
*/

IF UPDATE(Barcode)
  SELECT COUNT(*) [Concerned lines with barcode-change] 
    FROM inserted
ELSE SELECT COUNT(*) [No barcode-change] FROM inserted

GO

UPDATE Artikel SET Barcode = '4711' WHERE 1 = 2

GO

DROP TRIGGER TR_USER_AR_UPD_CountTest