Documentation
Back to website >

SQL Events (EULANDA SQL API)

Problems

A large number of SQL events can be intercepted and handled separately using triggers. It is possible to perform special checks when saving an address or to log changes to data. Triggers always work at table level. This restricts the possible area of application to events that primarily affect a table or the creation, change and deletion of one or more data records. Complex processes - such as converting an order into a delivery note - are difficult to grasp. To stick with the example: When converting an order into a delivery note, a number of individual actions are carried out within a transaction.

  • Creating the delivery note header
  • Create items or subitems
  • Credit limit check, if necessary
  • Setting the order status
  • Filling the conversion matrix between order items and delivery note items
  • If necessary, create titles that are used in the order
  • etc..

This list is exemplary and not complete. The actions to be performed vary from one EULANDA® version to the next. Surely you could write a trigger that performs a user-defined action when changing the job status. But we cannot ensure that all the other actions have already been carried out at this stage. For example, you cannot be sure that all delivery note items have been created. To counter this limitation of the trigger concept EULANDA® introduces a new class of events on server level. Below is a list of available events.

Server-side events

Eventid Event name Remark
10000 AfBuchen.OnSuccess Book Order
10003 AfEdit.OnSuccess Refine order
10004 AfStornoBuchung.OnSuccess Cancel order booking
10005 TraAfLf_DoJob.OnSuccess

Conversion of an order into a delivery note
NOTE: Depending on the settings, this event can be triggered after the delivery note has been booked. If posting-relevant changes have to be made, event 10019 must be used.

10006 TraLfRe_DoJob.OnSuccess Conversion of a delivery note into an invoice
10007 AnpCreate.OnSuccess Create a quotation item
10008 AfpCreate.OnSuccess Create an order item
10009 LfBuchen.OnSuccess Warehouse of delivery note is posted
10010 TraAnAf.OnSuccess After converting an offer into an order
10011 KfBuchen.OnSuccess When purchase order entry is completed (CTRL+B)
10012 KfpCreate.OnSuccess Create a purchase order item
10013 AfStornoRest.OnSuccess Cancel the remaining quantities in the order
10014 AfCreate.OnSuccess Creating an order, regardless of whether it is copied or created from scratch
10015 AnCreate.OnSuccess Creating an offer, regardless of whether it is copied or created from scratch
10016 CbBuchen.OnSuccess after posting the warehouse of a sales slip
10017 CbStornoBuchen.OnSuccess Cancellation of the warehouse posting for the cash document. This is mainly done when deleting an old cash document.
10018 CbBuchen.Before is called before the warehouse posting of the cash document. The"cnLbJob" table with the posting information is already filled. (RefTyp = "CB", RefId = @ObjId )
10019 LfBuchen.Before is called up before the delivery note is posted to stock.
10020 LfLdInit.OnSuccess Internal: Preparation of the warehouse posting of delivery notes
10021 JbLdInit.OnSuccess Internal: Preparation of the stock posting of BOMs
10022 LLInitAbLd.OnSuccess Internal: Preparation of the warehouse posting of warehouse documents (first posting)
10023 LLInitZuLd.OnSuccess Internal: Preparation of the warehouse posting of warehouse documents (second posting)
10024 ReStorno.OnSuccess After deleting invoices
10025 KfDelete.OnSuccess After deleting orders
10026 K.BeforefDelete Called before deleting purchase orders
10027 AfBuchen.Before Called before posting the order
10028 TraKfWe.OnSuccess After creating a delivery for a purchase order
10029 SpnInit.onSuccess After initialization of a SEPA message
10030 SpnRender.onSuccess After XML generation of the SEPA message
10031 TraAfLf_DoJob.BeforeBuchen In preparation
10032 ReStorno.Before In preparation - called up before the invoice is canceled to prevent this, if necessary
10033 LLBuchen.OnSuccess In preparation - after the successful posting of a warehouse document (goods receipt, physical inventory, delivery, etc.)
10034 LLZuBuchen.OnSuccess In preparation - after successful posting of the alternative posting of a warehouse document (goods receipt, physical inventory, delivery, and so on). Not used by standard modules
10035 LLStornoBuchen.Before In preparation - is called before a warehouse document is canceled. The original posting still exists at this time
10036 AfShopCreate.OnSuccess Is triggered after creating an order that has been received via the shop interface.
10037-10041 Versandübergabe reserved
10042 LfVersanduebergabe.OnHandover as of 7.0.19 is triggered after delivery
10043 LfVersanduebergabe.OnPrint as of 7.0.19 is triggered after printing the shipping labels
10044-10049 Versandübergabe reserved

The basic events were only introduced in EULANDA 2007. To get a list of events supported by your database, execute the following command:Supported events in your version

IF OBJECT_ID('cnEventType') IS NULL
  RAISERROR('SQL events are not yet supported in this version of Eulanda',16,1)
ELSE SELECT id,Name FROM cnEventType ORDER BY id

Asynchronous/delayed execution

As of version 7.0.19, the event procedure can also be called asynchronously. The original process that triggers the event is processed first. The procedure is then called after a specified period of time. To do this, a value greater than or equal to 0 must be entered in the column AsyncDeferredSeconds in the table cnEventSubscriber. This specifies the minimum waiting time in seconds until the procedure is executed.

The delayed execution requires that the procedure cn_Queue_HandleCnEvents is called regularly - e.g. every 5 minutes. In the Standard Edition of the SQL Server, this can be done via the SQL Server Agent. When using the Express Edition, this can be achieved via Windows task scheduling.

If the procedure is no longer executed in the same transaction of the original operation, it can of course no longer be terminated. If a value other than zero is returned, this is ignored or can be considered an error in future versions. Asynchronous execution is intended, for example, for time-consuming processes that should not unnecessarily delay the original processing (posting the delivery note, etc.). It is also recommended that you check whether the transferred object still exists and corresponds to the required status. It is quite possible, for example, that a delivery note has already been converted into an invoice or cancelled in the meantime.

Procedure Prototype

The handling of such an event is implemented as a procedure. The procedure must be strictly structured according to the following scheme:

CREATE PROC cn_user_evtXXX
  @SubsId int,
  @ObjType char(4),
  @ObjId int
AS

SET NOCOUNT ON



  RAISERROR('Error message',16,1)
  RETURN -1

  RETURN 0

Excample

A price check is implemented using the following SQL code that prevents the order from being posted if the price falls below the purchase price

CREATE PROC cn_user_evtAfBuchen_EkPruefung
  @SubsId int,
  @ObjType char(4),
  @ObjId int
AS

SET NOCOUNT ON

DECLARE @Pos int
SELECT TOP 1 @Pos = afp.PosNummer 
FROM dbo.AuftragPos afp
WHERE afp.KopfId = @ObjId AND afp.Basis > afp.VkRab

IF @@ROWCOUNT > 0
BEGIN
RAISERROR('The purchase price was not reached in position %d',16,1,@Pos)
RETURN -1
END ELSE RETURN 0

GO

INSERT cnEventSubscriber (EventId, Vendor, ProcName)
VALUES(10000, 'USER', 'cn_user_evtAfBuchen_EkPruefung')