Error creating Trigger
-
- Posts: 4
- Joined: Wed 29 Jun 2011 18:07
Error creating Trigger
This parameter gives error and new: and :old when having to create a trigger through code[/b]
-
- Posts: 4
- Joined: Wed 29 Jun 2011 18:07
Segue codigo
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;
' 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;
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:
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);
-
- Posts: 4
- Joined: Wed 29 Jun 2011 18:07
Ok
AlexP
Thank you, it worked just right ...
Thank you, it worked just right ...