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

EventidEvent nameRemark
10000AfBuchen.OnSuccessBook Order
10003AfEdit.OnSuccessRefine order
10004AfStornoBuchung.OnSuccessCancel order booking
10005TraAfLf_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.

10006TraLfRe_DoJob.OnSuccessConversion of a delivery note into an invoice
10007AnpCreate.OnSuccessCreate a quotation item
10008AfpCreate.OnSuccessCreate an order item
10009LfBuchen.OnSuccessWarehouse of delivery note is posted
10010TraAnAf.OnSuccessAfter converting an offer into an order
10011KfBuchen.OnSuccessWhen purchase order entry is completed (CTRL+B)
10012KfpCreate.OnSuccessCreate a purchase order item
10013AfStornoRest.OnSuccessCancel the remaining quantities in the order
10014AfCreate.OnSuccessCreating an order, regardless of whether it is copied or created from scratch
10015AnCreate.OnSuccessCreating an offer, regardless of whether it is copied or created from scratch
10016CbBuchen.OnSuccessafter posting the warehouse of a sales slip
10017CbStornoBuchen.OnSuccessCancellation of the warehouse posting for the cash document. This is mainly done when deleting an old cash document.
10018CbBuchen.Beforeis called before the warehouse posting of the cash document. The"cnLbJob" table with the posting information is already filled. (RefTyp = "CB", RefId = @ObjId )
10019LfBuchen.Beforeis called up before the delivery note is posted to stock.
10020LfLdInit.OnSuccessInternal: Preparation of the warehouse posting of delivery notes
10021JbLdInit.OnSuccessInternal: Preparation of the stock posting of BOMs
10022LLInitAbLd.OnSuccessInternal: Preparation of the warehouse posting of warehouse documents (first posting)
10023LLInitZuLd.OnSuccessInternal: Preparation of the warehouse posting of warehouse documents (second posting)
10024ReStorno.OnSuccessAfter deleting invoices
10025KfDelete.OnSuccessAfter deleting orders
10026K.BeforefDeleteCalled before deleting purchase orders
10027AfBuchen.BeforeCalled before posting the order
10028TraKfWe.OnSuccessAfter creating a delivery for a purchase order
10029SpnInit.onSuccessAfter initialization of a SEPA message
10030SpnRender.onSuccessAfter XML generation of the SEPA message
10031TraAfLf_DoJob.BeforeBuchenIn preparation
10032ReStorno.BeforeIn preparation - called up before the invoice is canceled to prevent this, if necessary
10033LLBuchen.OnSuccessIn preparation - after the successful posting of a warehouse document (goods receipt, physical inventory, delivery, etc.)
10034LLZuBuchen.OnSuccessIn 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
10035LLStornoBuchen.BeforeIn preparation - is called before a warehouse document is canceled. The original posting still exists at this time
10036AfShopCreate.OnSuccessIs triggered after creating an order that has been received via the shop interface.
10037-10041Versandübergabereserved
10042LfVersanduebergabe.OnHandoveras of 7.0.19 is triggered after delivery
10043LfVersanduebergabe.OnPrintas of 7.0.19 is triggered after printing the shipping labels
10044-10049Versandübergabereserved

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')