What is wrong with this code?


CREATE TRIGGER "MailAccountAfterDelete" AFTER DELETE
ON "MailAccounts"
BEGIN
 DECLARE MailFoldersCursor SENSITIVE CURSOR FOR MailFoldersStatement;

 PREPARE MailFoldersStatement FROM 'SELECT GUID FROM MailFolders WHERE MailFolders.AccountID = ?';
 OPEN MailFoldersCursor USING OLDROW.GUID;
 
 START TRANSACTION ON TABLES MailFolders;
 BEGIN
   WHILE NOT EOF(MailFoldersCursor) DO DELETE FROM MailFoldersCursor;
   END WHILE;
 
   COMMIT;
 
   EXCEPTION
     ROLLBACK;
 END;

END

I am getting an error when execute...

================================================================================
SQL Error (ElevateDB 1.05 Build 2)
================================================================================

ElevateDB Error #700 An error was found in the statement at line 7 and column 38
(Invalid expression . found, table qualifier not allowed)


P.S.  I have tried to use another field (not GUID). No luck anyway.

Alec,

<< I am getting an error when execute... >>

It's a bug.  Use this instead as a workaround:

CREATE TRIGGER "MailAccountAfterDelete" AFTER DELETE
ON "MailAccounts"
BEGIN
 DECLARE MailFoldersCursor SENSITIVE CURSOR FOR MailFoldersStatement;
 DECLARE TempGUID GUID DEFAULT OLDROW.GUID;

 PREPARE MailFoldersStatement FROM 'SELECT GUID FROM MailFolders WHERE
MailFolders.AccountID = ?';
 OPEN MailFoldersCursor USING TempGUID;

 START TRANSACTION ON TABLES MailFolders;
 BEGIN
   WHILE NOT EOF(MailFoldersCursor) DO DELETE FROM MailFoldersCursor;
   END WHILE;

   COMMIT;

   EXCEPTION
     ROLLBACK;
 END;

END

That helped...BUT only in 1.05

When I upaded to 1.06 this workaround is not working anymore.
Any of using "OLDROW." gets an error #700 (expected column name but instead found "OLDROW."."GUID").

And another question...even that workaround was working in 1.05 desired result of that trigger was not achieved. Any comments?


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Alec,

<< I am getting an error when execute... >>

It's a bug.  Use this instead as a workaround:

CREATE TRIGGER "MailAccountAfterDelete" AFTER DELETE
ON "MailAccounts"
BEGIN
 DECLARE MailFoldersCursor SENSITIVE CURSOR FOR MailFoldersStatement;
 DECLARE TempGUID GUID DEFAULT OLDROW.GUID;

 PREPARE MailFoldersStatement FROM 'SELECT GUID FROM MailFolders WHERE
MailFolders.AccountID = ?';
 OPEN MailFoldersCursor USING TempGUID;

 START TRANSACTION ON TABLES MailFolders;
 BEGIN
   WHILE NOT EOF(MailFoldersCursor) DO DELETE FROM MailFoldersCursor;
   END WHILE;

   COMMIT;

   EXCEPTION
     ROLLBACK;
 END;

END

Alec,

<< When I upaded to 1.06 this workaround is not working anymore.
Any of using "OLDROW." gets an error #700 (expected column name but instead
found "OLDROW."."GUID"). >>

Are you actually using OLDROW..GUID ?  The error message indicates that
there's an extra period after OLDROW.

<< And another question...even that workaround was working in 1.05 desired
result of that trigger was not achieved. Any comments? >>

I'm not sure what you mean here - are you saying that the DELETES are not
occurring in the trigger ?  If so, could you send me the database catalog
that you're using ?  That way I can test it with your metadata and trigger
definition.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim for response,

<<Are you actually using OLDROW..GUID ? >>

No, definetely no. It was just a copy/paste bug in this post.

Atually, regarding your advice in this topic I did changes in 1.05 (DECLARE TempGUID GUID DEFAULT OLDROW.GUID
At least I did not get an error anymore.
After upgrading to 1.06 I have tried to back to original code BUT even without any changes EDBManager gets an error as I described.
Once again, I have created that trigger in version 1.05. In version 1.06 I just cannot save this trigger without any changes.


<<I'm not sure what you mean here - are you saying that the DELETES are not
occurring in the trigger ?  If so, could you send me the database catalog
that you're using ?  That way I can test it with your metadata and trigger
definition.
>>

Let's back to this question when the first one will be resolved.


Best Regards,
Alec

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Alec,

<< When I upaded to 1.06 this workaround is not working anymore.
Any of using "OLDROW." gets an error #700 (expected column name but instead
found "OLDROW."."GUID"). >>

Are you actually using OLDROW..GUID ?  The error message indicates that
there's an extra period after OLDROW.

<< And another question...even that workaround was working in 1.05 desired
result of that trigger was not achieved. Any comments? >>

I'm not sure what you mean here - are you saying that the DELETES are not
occurring in the trigger ?  If so, could you send me the database catalog
that you're using ?  That way I can test it with your metadata and trigger
definition.

--
Tim Young
Elevate Software
www.elevatesoft.com

Alec,

<<  Let's back to this question when the first one will be resolved. >>

Well, I can't guarantee that the problem will be resolved for you unless I
can reproduce it here and verify what you're seeing.   1.06 was tested with
the trigger test for the OLROW. issue, and it is part of our automated
regression tests.  Which means that the issue is something else since 1.06
was tested for the compilation issue.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

I have created simple database for testing.
There are two tables MasterTable and DetailTable. DetailTable has ParentID field which point to the ID field of MasterTable (just standard relations).
MasterTable has a trigger "AfterDeleteMaster" which does deletion of DetailTable records. When I delete Master record nothing happens with the DetailTable.

If you could find what is wrong there will be great.

Regarding my first question...I found that I can add a new trigger without any errors (related to OLDROW.). But when I am trying to alter just added trigger I have an error as I pointed in my
initial post. To alter that trigger I have to drop it first and add that again. Not good but I can stay with that.

Thanks.





"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Alec,

<<  Let's back to this question when the first one will be resolved. >>

Well, I can't guarantee that the problem will be resolved for you unless I
can reproduce it here and verify what you're seeing.   1.06 was tested with
the trigger test for the OLROW. issue, and it is part of our automated
regression tests.  Which means that the issue is something else since 1.06
was tested for the compilation issue.

--
Tim Young
Elevate Software
www.elevatesoft.com

Alec,

<< I have created simple database for testing.
There are two tables MasterTable and DetailTable. DetailTable has ParentID
field which point to the ID field of MasterTable (just standard relations).
MasterTable has a trigger "AfterDeleteMaster" which does deletion of
DetailTable records. When I delete Master record nothing happens with the
DetailTable. >>

Okay, I've got it now.  The issue is the lack of a fetch, although it
shouldn't really be needed and EDB should handle the default fetching for
you.  However, here is the workaround trigger:

BEGIN
 DECLARE FCursor SENSITIVE CURSOR FOR FStatement;
 DECLARE TEMPGUID VARCHAR(40) DEFAULT OLDROW.ID;

 PREPARE FStatement FROM 'SELECT * FROM DetailTable WHERE ParentID = ?';
 OPEN FCursor USING TEMPGUID;
 FETCH FIRST FROM FCursor;

 START TRANSACTION ON TABLES DetailTable;
 BEGIN
   WHILE NOT EOF(FCursor) DO
     DELETE FROM FCursor;
     FETCH FIRST FROM FCursor;
   END WHILE;

   COMMIT;

   EXCEPTION
     ROLLBACK;
 END;
END

Notice the FETCH FIRST calls inserted.

<< Regarding my first question...I found that I can add a new trigger
without any errors (related to OLDROW.). But when I am trying to alter just
added trigger I have an error as I pointed in my initial post. To alter that
trigger I have to drop it first and add that again. Not good but I can stay
with that. >>

Yes, I was able to reproduce that also - the ALTER TRIGGER statement is
missing a couple of minor setup calls for the compilation that is causing
the error.  The workaround for now is to DROP and then CREATE the trigger as
noted above.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

This is exactly what I need for now.
It works.

Thank you for your help.


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Alec,

<< I have created simple database for testing.
There are two tables MasterTable and DetailTable. DetailTable has ParentID
field which point to the ID field of MasterTable (just standard relations).
MasterTable has a trigger "AfterDeleteMaster" which does deletion of
DetailTable records. When I delete Master record nothing happens with the
DetailTable. >>

Okay, I've got it now.  The issue is the lack of a fetch, although it
shouldn't really be needed and EDB should handle the default fetching for
you.  However, here is the workaround trigger:

BEGIN
 DECLARE FCursor SENSITIVE CURSOR FOR FStatement;
 DECLARE TEMPGUID VARCHAR(40) DEFAULT OLDROW.ID;

 PREPARE FStatement FROM 'SELECT * FROM DetailTable WHERE ParentID = ?';
 OPEN FCursor USING TEMPGUID;
 FETCH FIRST FROM FCursor;

 START TRANSACTION ON TABLES DetailTable;
 BEGIN
   WHILE NOT EOF(FCursor) DO
     DELETE FROM FCursor;
     FETCH FIRST FROM FCursor;
   END WHILE;

   COMMIT;

   EXCEPTION
     ROLLBACK;
 END;
END

Notice the FETCH FIRST calls inserted.

<< Regarding my first question...I found that I can add a new trigger
without any errors (related to OLDROW.). But when I am trying to alter just
added trigger I have an error as I pointed in my initial post. To alter that
trigger I have to drop it first and add that again. Not good but I can stay
with that. >>

Yes, I was able to reproduce that also - the ALTER TRIGGER statement is
missing a couple of minor setup calls for the compilation that is causing
the error.  The workaround for now is to DROP and then CREATE the trigger as
noted above.

--
Tim Young
Elevate Software
www.elevatesoft.com