Page 1 of 1
Error creating Trigger
Posted: Wed 29 Jun 2011 18:15
by GRAZIANI SESANA
This parameter gives error and new: and :old when having to create a trigger through code[/b]
Posted: Wed 29 Jun 2011 18:51
by Rodolpho
Hi,
Post your code and examples for more details to help you.
Segue codigo
Posted: Wed 29 Jun 2011 19:01
by GRAZIANI SESANA
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;
Posted: Wed 29 Jun 2011 20:24
by Rodolpho
You forgot of Error Message
Posted: Thu 30 Jun 2011 09:05
by AlexP
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);
Ok
Posted: Thu 30 Jun 2011 11:21
by GRAZIANI SESANA
AlexP
Thank you, it worked just right ...