![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
![]() |
Fri, Mar 27 2009 12:12 PM | Permanent Link |
Laurent | 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. |
Fri, Mar 27 2009 1:12 PM | Permanent Link |
Fernando Dias ![]() | 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] |
Fri, Mar 27 2009 2:42 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |