Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
3
Erster Beitrag:
vor 2 Jahren, 1 Monat
Letzter Beitrag:
vor 2 Jahren, 1 Monat
Beteiligte Autoren:
Arie, Curtis

HFSQL20 server triggers

Startbeitrag von Arie am 05.01.2016 10:58

Hi, is anyone effectively using server triggers?

I want to use a server trigger to keep track of changed records in a dozen tables.
The trigger is saving the tablename + unique keyvalue in a separate tbl_journal I created.

I'm using the code below in the trigger and defined it as an after-modify/add/delete trigger on several tables.

However in 3 days and after about 5000 records I already noticed 2 failures.
1. the triggercode was gone in the hfsql database (something like "procedure xxxx not found when executing trigger")
I had to add the code again using wdmodfic

2. an error saying "Internal error of WDVM DLL."
After disabling the triggers the error was gone, but then I miss my trigger functionality of course (I could not restart the hfsql engine because of numerous active users in other database)

So it seems to me the servertriggers are NOT reliable. Any comments?


PROCEDURE AddToJournal()

sTable is string
sMode is string
nRecordUID is int
nRecordUID2 is int
sField is string

WHEN EXCEPTION IN
sTable = H.FileName

SWITCH Upper(sTable)
CASE "TBL_ADRESSEN"
sField = Tbl_Adressen.AdresID..Name
nRecordUID = Tbl_Adressen.AdresID
CASE "TBL_DP"
sField = "TBL_DP"
nRecordUID = "DPID"
CASE "TBL_FIBERDATA"
sField = Tbl_Fiberdata.FiberdataID..Name
nRecordUID = Tbl_Fiberdata.FiberdataID
CASE "TBL_OPDRACHTEN"
sField = Tbl_Opdrachten.OpdrachtID..Name
nRecordUID = Tbl_Opdrachten.OpdrachtID
CASE "TBL_OPDRACHTENSTATUS"
sField = Tbl_OpdrachtenStatus.OpdrachtenStatusID..Name
nRecordUID = Tbl_OpdrachtenStatus.OpdrachtenStatusID
CASE "TBL_OPDRACHTENPLANNING"
sField = Tbl_OpdrachtenPlanning.PlanningID..Name
nRecordUID = Tbl_OpdrachtenPlanning.PlanningID
CASE "TBL_OPDRACHTENPLANNINGSTATUS"
sField = Tbl_OpdrachtenPlanningStatus.OpdrachtenPlanningStatusID..Name
nRecordUID = Tbl_OpdrachtenPlanningStatus.OpdrachtenPlanningStatusID
CASE "TBL_PARAMETERS"
sField = Tbl_Parameters.ParametersID..Name
nRecordUID = Tbl_Parameters.ParametersID
CASE "TBL_PROJECTEN"
sField = Tbl_Projecten.ProjectenID..Name
nRecordUID = Tbl_Projecten.ProjectenID
CASE "TBL_PROJECTEN_X_ADRESSEN"
sField = Tbl_Projecten_X_Adressen.IDTbl_Adressen_Tbl_Projecten..Name
nRecordUID = Tbl_Projecten_X_Adressen.IDTbl_Adressen_Tbl_Projecten
CASE "TBL_REDENEN"
sField = Tbl_Redenen.RedenID..Name
nRecordUID = Tbl_Redenen.RedenID
CASE "TBL_WERKNEMERS"
sField = Tbl_Werknemers.WerknemersID..Name
nRecordUID = Tbl_Werknemers.WerknemersID
CASE "TBL_WONINGCORPORATIES"
sField = Tbl_WoningCorporaties.WoningCorporatieID..Name
nRecordUID = Tbl_WoningCorporaties.WoningCorporatieID
CASE "TBL_M_FIBERDATA"
sField = Tbl_M_Fiberdata.FiberdataID_M..Name
nRecordUID = Tbl_M_Fiberdata.FiberdataID_M

END

IF sField "" THEN
HReset(Tbl_SyncJournal)
Tbl_SyncJournal.KoppelTabel = sTable
Tbl_SyncJournal.Actie = sMode
Tbl_SyncJournal.KoppelID = nRecordUID
Tbl_SyncJournal.KoppelID2 = nRecordUID2
Tbl_SyncJournal.Koppelveld = sField
Tbl_SyncJournal.CreationDateTime = DateSys()+TimeSys()
IF NOT WL.HAdd(Tbl_SyncJournal) THEN
LogbookEvent(dbgInfo(dbgProcess),ErrorInfo(errCode),ErrorInfo(errMessage),1)
END
END

DO
s is string = DateToString(DateSys(),"DD-MM-YYYY") + " " + TimeToString(TimeSys(),"HH:MM:SS")
s += CR + dbgInfo(dbgProcess)
s += CR + ErrorInfo(errFullDetails)
WriteErrorToAscii(s) //
END

Antworten:

I only have experience with client side triggers, but sql queries won't set off the trigger. Not sure if it's the same for server side.

But you probably already know that, just a thought.

von Curtis - am 05.01.2016 15:23
Curtis,

serverside trigger do get triggered by sql statement, that's the main reason for me. Because I also have a website and webservice in this project.
But if not 100% reliable I may have to go for your option or even override HAdd and so on. In that case I have to look for add/modify queries in my project, and add "journal" statement too.

von Arie - am 05.01.2016 18:41
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.