This discussion is archived
5 Replies Latest reply: Feb 5, 2013 2:42 PM by 989346 RSS

Unable to create Triggers when Oracle oleDB drivers are used

979724 Newbie
Currently Being Moderated
My Delphi application creates procedures and triggers in to MSSQL and Oracle database. We use ADO as the data access layer and Oracle.ole DB(oraoleDB.oracle.1) as provider when we insert procedures into an Oracle DB. Some of the triggers/Procedure are executed successfully but some do not. However there is no error via the ADO components when the command is executed. But they are simply not created in the database. Below is the sample code:

procedure CreateTriggers
var
qry : TADOCommand;
begin

qry := TADOCommand.Create(self);
try
qry.Connection := MyconnectionString
// something like this
//(Provider=OraOLEDB.Oracle;Data Source=xxx;User ID=xxx;Password=xx;
//Extended properties="NDatatype=true")

qry.ParamCheck := false;
qry.CommandText := MyMemo.Text;
qry.Execute;
finally
qry.free
end;
end;

The text in the memo/Trigger is something like this:

Create Or Replace Trigger
MYTrigger
Before Update
On
MyTable
Referencing
New As MyNew
Old As MyOld
For Each Row
Declare
v_new_updatecount MyTable.UPDATECOUNT%TYPE;
Begin
GET_NEW_UPDATECOUNT(:MyOld.UPDATECOUNT, v_new_updatecount);

:MyNew.UPDATECOUNT := v_new_updatecount;
End;



This is only a problem with Oracle and when i use OLEDDB drivers. When MSDORA is used then there are no issues
When i remove the colons from the trigger then , the trigger is executed successfully.

Any suggestions what is going wrong?
  • 1. Re: Unable to create Triggers when Oracle oleDB drivers are used
    gdarling - oracle Expert
    Currently Being Moderated
    What version of OLEDB provider are you using? I tried this simple test using vbscript and 11203 oledb, and it worked for me..
    'create table twocol(col1 number, col2 number);
    set con = createobject("adodb.connection")
    con.open "provider=oraoledb.oracle;user id=scott;password=tiger;data source=orcl"
    set cmd = createobject("adodb.command")
    cmd.activeconnection = con
    cmd.commandtext = "create or replace trigger twocoltrig " & _
          "before insert on twocol " & _
           "for each row " & _
           "begin " & _
             ":new.col2:=123;" & _
           "end;"
    cmd.execute 
    wscript.echo "done, no errors"
    double check:
    -------------------
    SQL> select status from user_triggers where trigger_name='TWOCOLTRIG';

    STATUS
    --------
    ENABLED

    Edited by: gdarling on Jan 31, 2013 8:25 AM
  • 2. Re: Unable to create Triggers when Oracle oleDB drivers are used
    979724 Newbie
    Currently Being Moderated
    We are using 11.2.0.2 version of OLEDB.
  • 3. Re: Unable to create Triggers when Oracle oleDB drivers are used
    979724 Newbie
    Currently Being Moderated
    when i executed the trigger

    create or replace trigger twocoltrig
    before insert on twocol
         for each row
         begin
         :new.col2:=123;
         end;

    every thing went fine and the trigger was created in the database

    After some investigation i changed my trigger to the following

    Create Or Replace Trigger
    MYTrigger
    Before Update
    On
    MyTable
    For Each Row
    Declare
    v_new_updatecount MyTable.UPDATECOUNT%TYPE;
    Begin
    GET_NEW_UPDATECOUNT(:Old.UPDATECOUNT, v_new_updatecount);

    :New.UPDATECOUNT := v_new_updatecount;
    End;

    now too the trigger was created in the database.

    so this has to do some thing with the referencing part of it and then using the new variables, though this is a valid syntax.

    ( Referencing
    New As MyNew
    Old As MyOld )

    any suggestions on this?

    Edited by: Murthy Gudivada on Jan 31, 2013 9:31 PM
  • 4. Re: Unable to create Triggers when Oracle oleDB drivers are used
    989346 Newbie
    Currently Being Moderated
    Delphi application creates procedures and triggers in to MSSQL and Oracle database. We use ADO as the data access layer and Oracle.ole DB(oraoleDB.oracle.1) as provider when we insert procedures into an Oracle DB. Some of the triggers/Procedure are executed successfully but some do not. However there is no error via the ADO components when the command is executed. But they are simply not created in the database. Below is the sample code
    ======================
    for printer ink

    Edited by: 986343 on Feb 5, 2013 2:39 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points