Error creating Trigger

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
GRAZIANI SESANA
Posts: 4
Joined: Wed 29 Jun 2011 18:07

Error creating Trigger

Post by GRAZIANI SESANA » Wed 29 Jun 2011 18:15

This parameter gives error and new: and :old when having to create a trigger through code[/b]

Rodolpho
Posts: 9
Joined: Wed 15 Jun 2011 13:25
Contact:

Post by Rodolpho » Wed 29 Jun 2011 18:51

Hi,

Post your code and examples for more details to help you.

GRAZIANI SESANA
Posts: 4
Joined: Wed 29 Jun 2011 18:07

Segue codigo

Post by GRAZIANI SESANA » Wed 29 Jun 2011 19:01

CDS_Banco.CommandText := ('CREATE OR REPLACE TRIGGER gs_trg_estoque'+
' AFTER INSERT OR DELETE'+
' ON pcmov'+
' REFERENCING NEW AS NEW OLD AS OLD'+
' FOR EACH ROW'+
' BEGIN'+
' IF INSERTING THEN'+
' UPDATE PCEST SET'+
' pcest.QTESTger = (pcest.QTESTger - :new.qt)'+
' ,'+
' pcest.qtest = (pcest.qtest - :new.qt)'+
' WHERE pcest.CODPROD = :new.codprod AND :new.ROTINALANC = ''GS_208.exe'' AND pcest.codfilial = :new.codfilial;'+
' ELSIF UPDATING THEN'+
' UPDATE PCEST SET'+
' pcest.QTESTger = ( (pcest.QTESTger + :old.qt) - :new.qt)'+
' ,'+
' pcest.qtest = ( (pcest.QTEST + :old.qt) - :new.qt)'+
' WHERE pcest.CODPROD = :new.codprod AND :new.ROTINALANC = ''GS_208.exe'' AND pcest.codfilial = :new.codfilial;'+
' ELSIF DELETING THEN'+
' UPDATE PCEST SET'+
' pcest.QTESTger = (pcest.QTESTger + :old.qt)'+
' ,'+
' pcest.qtest = (pcest.QTEST + :old.qt)'+
' WHERE pcest.CODPROD = :old.codprod AND :old.ROTINALANC = ''GS_208.exe'' AND pcest.codfilial = :old.codfilial;'+
' END IF;'+
' END;');

CDS_Banco.Execute;

Rodolpho
Posts: 9
Joined: Wed 15 Jun 2011 13:25
Contact:

Post by Rodolpho » Wed 29 Jun 2011 20:24

You forgot of Error Message

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by AlexP » Thu 30 Jun 2011 09:05

Hello,

This error occurs because the :new and :old expressions, as well as any other expression before which the “:” symbol is specified, is interpreted as a parameter.
To solve this problem, you should use the SQLConnection.Execute method to create triggers, for example:

Code: Select all

 SQLConnection1.Execute('CREATE OR REPLACE TRIGGER TR_BI_TEST'+#13+
                        'BEFORE INSERT'+#13+
                        'ON TEST'+#13+
                        'REFERENCING OLD AS old NEW AS new'+#13+
                        'FOR EACH ROW'+#13+
                        'BEGIN'+#13+
                        'SELECT SQ_TEST.NEXTVAL INTO :new.ID FROM DUAL;'+#13+
                        'END;', nil);

GRAZIANI SESANA
Posts: 4
Joined: Wed 29 Jun 2011 18:07

Ok

Post by GRAZIANI SESANA » Thu 30 Jun 2011 11:21

AlexP

Thank you, it worked just right ...

Post Reply