Hi All

First, many thanks for your fantastic support...

I'm attempting to do a controlled loading of the updates as follows:

Each table involved in replication has its own ordinary triggers and 6 more triggers to control loading updates.

The ordinary triggers are triggered when LoadingUpdates() is False like this

CREATE TRIGGER "TRIGGER_1" BEFORE INSERT ON "TABLE_1"
WHEN NOT LOADINGUPDATES()
BEGIN

There are 3 control triggers which are triggered when loading updates like this

CREATE TRIGGER "LOADUPDATE_TRIGGER_1" BEFORE INSERT ON "TABLE_1"
WHEN LOADINGUPDATES()
BEGIN
   IF NEWROW.REC_USR  <> 'ABC' THEN
       RAISE ERROR CODE 10000 MESSAGE ''Invalid Record'';
   END IF;
END';

CREATE TRIGGER "LOADUPDATE_TRIGGER_2" BEFORE UPDATE ON "TABLE_1"
WHEN LOADINGUPDATES()
BEGIN
   IF NEWROW.REC_USR  <> 'ABC' THEN
       RAISE ERROR CODE 10000 MESSAGE ''Invalid Record'';
   END IF;
END';


CREATE TRIGGER "LOADUPDATE_TRIGGER_3" BEFORE DELETE ON "TABLE_1"
WHEN LOADINGUPDATES()
BEGIN
   IF OLDROW.REC_USR  <> 'ABC' THEN
       RAISE ERROR CODE 10000 MESSAGE ''Invalid Record'';
   END IF;
END';

Also, I have 3 Error Triggers set to trigger when loading updates, if there was an error and do nothing like this:

CREATE TRIGGER "ERROR_TRIGGER_1" ERROR INSERT ON "TABLE_1"
WHEN LOADINGUPDATES()
BEGIN
END';

CREATE TRIGGER "ERROR_TRIGGER_2" ERROR UPDATE ON "TABLE_1"
WHEN LOADINGUPDATES()
BEGIN
END';

CREATE TRIGGER "ERROR_TRIGGER_3" ERROR DELETE ON "TABLE_1"
WHEN LOADINGUPDATES()
BEGIN
END'

So when a record is about to be processed as part of Loading updates and REC_USR  <> 'ABC'  then Exception is meant to be raised, which it does, but unfortunetly the Error Triggers are not swallowing up the errors. But instead multiple Exceptions are being raised inside the application which called the Stored procedure which is doing the replication.

Is that by design, or misunderstanding on my behalf? Is there a way to raise the error in a trigger and capture it in the Error trigger and not let it escape or raise an exception inside the application?

Many Thanks

Alfred

Alfred,

<< So when a record is about to be processed as part of Loading updates and
REC_USR  <> 'ABC'  then Exception is meant to be raised, which it does, but
unfortunetly the Error Triggers are not swallowing up the errors. But
instead multiple Exceptions are being raised inside the application which
called the Stored procedure which is doing the replication.

Is that by design, or misunderstanding on my behalf? Is there a way to raise
the error in a trigger and capture it in the Error trigger and not let it
escape or raise an exception inside the application? >>

Well, it's by-design due to one aspect - it is expected that the exceptions
will come from the actual INSERTs, UPDATEs, or DELETEs, and not from the
other triggers.  The AFTER trigger exceptions will get caught by the ERROR
triggers simply due to the way that it is coded, but not the BEFORE
triggers.

I think I can move the BEFORE triggers into the exception block without
issue, however, so I'll see about adding this for the next build.  The only
potential downside is that the BEFORE triggers will then constantly execute
for every RETRY, but that's probably what most developers would expect to
happen, so that isn't really a downside.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Many thanks for the reply.

Yes, it will be great if you can do that. Then this can act as an additional mechanisim for controlling the LoadingUpdates functionality in so many ways...

Kind Regards

Alfred

Alfred Ghazzi wrote:

Hi Tim,

Many thanks for adding the feature.

For some reason, it is not working.

A local table has the following

1) A trigger which is triggered when NOT LOADINGUPDATES on Before Insert
2) A trigger which is triggered when LOADINGUPDATES on Before ALL (I tried with individual triggers, same result)
BEGIN
  IF OLDROW.REC_USR  <> 'HOAG' THEN
     RAISE ERROR CODE 11000 MESSAGE 'Record Excluded';
  END IF;
  IF NEWROW.REC_USR  <> 'HOAG' THEN
     RAISE ERROR CODE 11000 MESSAGE 'Record Excluded';
  END IF;
END

3) A trigger which is triggered when LOADINGUPDATES on Error ALL (I tried with individual triggers, same result)
BEGIN
  (The error trigger is empty (does nothing - just to swallow the exceptions)
END

I started with clean Local and Server databases.

I inserted a record in the Server database that belongs to user 'HODN' (the field REC_USR is set to 'HODN' and its Primary Key = 'HODN-S1')

I saved Server updates and attempted to load them into Local database

It raises this error: "ElevateDB Error #1305. An Error occured with the statement at line 79 and cloumn 31 (Error loading the updates for the database Local (No row found in the table AAA with the primary key values of HODN-S1))."

My understanding is that the engine is complaining due to an attempt to update a record with a primary key of 'HODN-S1', which we know it does not exist in Local database.

The question is why it is even trying to update? The second trigger should have been triggered before any insert/update/delete attempt and raised the exception becuase REC_USR <> 'HOAG' (it is equal to 'HODN')?

Kind Regards and Many Thanks

Alfred Ghazzi

Kind Regards

Alfred

Alfred,

<< For some reason, it is not working.  >>

It's a different issue - the error that you're seeing:

"ElevateDB Error #1305. An Error occured with the statement at line 79 and
cloumn 31 (Error loading the updates for the database Local (No row found in
the table AAA with the primary key values of HODN-S1))."

is generated by the replication, not by an update directly.   Therefore, it
won't be caught by any of the triggers.

The replication can't start an update on a row that it can't find to begin
with, and no update, no triggers.

I'll see if I can add something that will fire a trigger in such a case, but
no guarantees and it will have to be part of 2.04 or higher, which will be
finally released by Wednesday.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim

Many thanks for your reply...

Since I posted the message, I realized I was somewhat incomplete in my description. My apologies for that.

Therefore, I created a sample databases to demonstrate the issue. The scripts for creating the databases are attached. I was able to generate the same error with generic structures.

1) Please, in the manager create a new session

2) Execute Script One after updating paths etc...

3) Execute Script Two (Sample Server Database >> DBS)

4) Execute Script Three (Sample Local Database >> DBL)

5) Execute DBS database procedure "Save_Updates" followed by DBL database procedure "Load_Updates".

An error will be raised: Error # 1305... (Error loading the update for database DBL (No row found in the table Table1 with the primary Key values of HODN-S2)).

NOTE: Without Table2 all works well. But when I introduced Table2 with its updating triggers (Triggers1, 2, 3 - which update Field2 in Table1) the exception gets raised.

I thought this could shed more light on the issue.

Many Thanks and Kind Regards

Alfred GHazzi

Alfred,

<< Therefore, I created a sample databases to demonstrate the issue. The
scripts for creating the databases are attached. I was able to generate the
same error with generic structures. ..

It's like I said before, the issue is this:

1) On the first load (INSERT), the row is excluded because of your BEFORE
trigger that raises the exception, which is eaten by the ERROR trigger.

2) On the second load (UPDATE), the loading of the updates cannot find the
first row because it wasn't inserted, so it doesn't even start the UPDATE
process because there isn't any row to update, thus you get a LOAD error,
not an UPDATE error, and your triggers are never fired.

The LOAD process is entirely separate from the UPDATE/DELETE process, and
requires that a row with a given primary key exist before it can start the
UPDATE process.  That's why I said that I need to see if I can come up a
LOAD trigger, or somehow unite the two processes so that the triggers can
encompass both.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim

Many thanks for the clarification. Also, many thanks for considering a solution for it.

Kind Regards

Alfred

Alfred,

<< Many thanks for the clarification. Also, many thanks for considering a
solution for it. >>

I should be able to come up with something.  It's a commonly-requested item
also since this type of thing comes up often during replication.

--
Tim Young
Elevate Software
www.elevatesoft.com