How to use cursor in a trigger for delete in Firebird?

Question

I tried to write a trigger in Firebird in order to delete records from other tables automatically when I delete records from a table. Just like delete by cascade. For some reasons, there are some tables without primary keys.

The trigger is just like this:

CREATE TRIGGER TRG1_DEL_STEP_INFO FOR STEP_INFO
ACTIVE AFTER DELETE
AS
DECLARE VARIABLE del_ID_IMAGE_INFO bigint;
DECLARE VARIABLE del_ID_FRAME_INFO bigint;

DECLARE del_cursor CURSOR FOR (SELECT ID_IMAGE_INFO, ID_FRAME_INFO FROM OLD);

    BEGIN

       OPEN del_cursor;

       WHILE (ROW_COUNT > 0) DO

         BEGIN

           FETCH del_cursor INTO del_ID_IMAGE_INFO, del_ID_FRAME_INFO;

             DELETE FROM FRAME_POLYGON WHERE ID_FRAME_INFO = :del_ID_FRAME_INFO;

             DELETE FROM FRAME_INFO WHERE RECID = :del_ID_FRAME_INFO;

             DELETE FROM IMAGE_INFO WHERE RECID = :del_ID_IMAGE_INFO;

             DELETE FROM IMAGE_FILE_INFO WHERE ID_IMAGE_INFO = :del_ID_IMAGE_INFO;

          END

         CLOSE del_cursor;
    END

But when I tried to put this into my Firebird DB by isql, I got a error like this:

Statement failed, SQLSTATE = 42S02
Dynamic SQL Error
-SQL error code = -204
-Table unknown
-OLD
-At line 6, column 73

Could you please to tell me how fix this problem?

Answer 1

You can't use a cursor for this, nor do you need a cursor. Triggers in Firebird are fired per row, not per statement.

So instead you should do:

CREATE TRIGGER TRG1_DEL_STEP_INFO FOR STEP_INFO
ACTIVE AFTER DELETE
AS
BEGIN
    DELETE FROM FRAME_POLYGON WHERE ID_FRAME_INFO = OLD.ID_FRAME_INFO;
    DELETE FROM FRAME_INFO WHERE RECID = OLD.ID_FRAME_INFO;
    DELETE FROM IMAGE_INFO WHERE RECID = OLD.ID_IMAGE_INFO;
    DELETE FROM IMAGE_FILE_INFO WHERE ID_IMAGE_INFO = OLD.ID_IMAGE_INFO
END