K
Khansolo
Guest
We have the following situation:
Our primary database is an OpenEdge one, and we have a SQL database which we have to sync up with our OE one. We came up with a plan to set up staging tables for our OE tables where we'd copy newly created records with the addition of a couple extra fields for logging the user that created the record, dates, etc. We've managed to connect those two databases using the OE DataServer, but we're having trouble with setting up triggers that would find the newly created records and copy them to the staging tables and assigning values to those additional logging fields.
We tried using RECID as an identifier for the newly created record and came up with a trigger procedure which would handle that. The thing is, when ran as a procedure, it works just fine. It finds the record and copies everything to the staging table without a problem. But when we set up the CREATE trigger on the database to run that procedure, we get errors.
The procedure looks like this:
DEFINE VARIABLE cRECID AS RECID NO-UNDO. DEFINE VARIABLE cUser AS CHARACTER NO-UNDO.
cUser = USERID("maticna").
FOR EACH Artikli NO-LOCK BY RECID(Artikli): ASSIGN cRECID = RECID(Artikli). END.
IF cRECID <> ? THEN DO: FOR EACH Artikli WHERE RECID(Artikli) = cRECID :
END.
When trying to create a new Artikli record with a simple procedure like:
CREATE Artikli. ASSIGN Artikli.SifArtik = "12345678" Artikli.Name = "Test" etc...
it throws an error saying Artikli.SifArtik is mandatory, but has unknown (?) value. (110)
Continue reading...
Our primary database is an OpenEdge one, and we have a SQL database which we have to sync up with our OE one. We came up with a plan to set up staging tables for our OE tables where we'd copy newly created records with the addition of a couple extra fields for logging the user that created the record, dates, etc. We've managed to connect those two databases using the OE DataServer, but we're having trouble with setting up triggers that would find the newly created records and copy them to the staging tables and assigning values to those additional logging fields.
We tried using RECID as an identifier for the newly created record and came up with a trigger procedure which would handle that. The thing is, when ran as a procedure, it works just fine. It finds the record and copies everything to the staging table without a problem. But when we set up the CREATE trigger on the database to run that procedure, we get errors.
The procedure looks like this:
DEFINE VARIABLE cRECID AS RECID NO-UNDO. DEFINE VARIABLE cUser AS CHARACTER NO-UNDO.
cUser = USERID("maticna").
FOR EACH Artikli NO-LOCK BY RECID(Artikli): ASSIGN cRECID = RECID(Artikli). END.
IF cRECID <> ? THEN DO: FOR EACH Artikli WHERE RECID(Artikli) = cRECID :
Code:
FIND FIRST stagingArtikli WHERE RECID(stagingArtikli) = cRECID NO-LOCK NO-ERROR.
IF NOT AVAILABLE stagingArtikli THEN
DO:
CREATE stagingArtikli.
BUFFER-COPY Artikli TO stagingArtikli.
ASSIGN
stagingArtikli.CreatedBy = cUser
stagingArtikli.CreateDate = NOW
stagingArtikli.Replicated = NO
stagingArtikli.Edited = NO
stagingArtikli.EditDate = ?
stagingArtikli.EditUser = " "
.
END.
END.END.
When trying to create a new Artikli record with a simple procedure like:
CREATE Artikli. ASSIGN Artikli.SifArtik = "12345678" Artikli.Name = "Test" etc...
it throws an error saying Artikli.SifArtik is mandatory, but has unknown (?) value. (110)
Continue reading...