Hello,

In relation of 2 tables (master / detail) I need to delete all detail after master row is
deleted.
For this I use trigger AFTER_DELETE in master table:

BEGIN
 DECLARE TriggerCursor SENSITIVE CURSOR FOR TriggerStatement;
 DECLARE S VARCHAR DEFAULT '';
       
 PREPARE TriggerStatement FROM 'SELECT * FROM AutorisProjet Where Projet=''' +
OLDROW.Projet + '''';
 OPEN TriggerCursor;
                                                                               
 FETCH FIRST FROM TriggerCursor INTO S;
 WHILE NOT EOF(TriggerCursor) DO
   DELETE FROM TriggerCursor;
   FETCH RELATIVE 0 FROM TriggerCursor INTO S;
 END WHILE;
END

This work fine but in French language the "Projet" field can contain one or multiple
single cote. Example: "A l’aide". This value locks the trigger.
The function QuotedStr(OLDROW.Projet) is not usable in trigger.
How to do for this ?

Thank you for your help.

Laurent,

The direct answer to your question is: use a parameterized statement instead,
like this:

BEGIN
  DECLARE TriggerCursor SENSITIVE CURSOR FOR TriggerStatement;
  DECLARE S VARCHAR DEFAULT '';

  PREPARE TriggerStatement FROM 'SELECT * FROM AutorisProjet Where Projet = ? ';

  OPEN TriggerCursor USING OLDROW.Projet;

  FETCH FIRST FROM TriggerCursor INTO S;
  WHILE NOT EOF(TriggerCursor) DO
    DELETE FROM TriggerCursor;
    FETCH RELATIVE 0 FROM TriggerCursor INTO S;
  END WHILE;
END


However, in addition I'd like to add some comments:

1.
In order to properly implement cascading deletes, you should use a BEFORE DELETE
trigger instead of an AFTER DELETE. That will prevent "master" rows from being
deleted if a "detail" row can't be deleted because, for example, it's locked.

2.
You do not need to use a cursor, etc. to accomplish the deletes.
All you need is this:

BEGIN
  DECLARE TriggerStatement STATEMENT;
  PREPARE TriggerStatement FROM 'DELETE FROM AutorisProjet Where Projet = ? ';
  EXECUTE TriggerStatement USING OLDROW.Projet;
END


--
Fernando Dias
[Team Elevate]

Laurent,

In addition to Fernando's answer - ElevateDB 2.03 will include a native
QuotedStr() function for scripts, procedures, triggers, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com