Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2007
    Location
    Vilnius, Lithuania
    Posts
    10

    Unanswered: can't create a trigger

    hello, i've been looking for a way to solve my problem for a few hours and had no luck, so decided to post here and i hope someone will help me
    so here's the deal. i'm writing a simple oracle XE database client in delphi and am trying to create a table with a primary key. i can create a table, set the primary key, create a new sequence, but i keep getting errors on creating a trigger. oracle object browser gives me the following sql to create the trigger:

    CREATE trigger "BI_TEST"
    before insert on "TEST"
    for each row
    begin
    select "TEST_SEQ".nextval into :NEW.ID from dual;
    end;

    and this works fine.

    in delphi i use ADOCommand and set the commandtext property to:

    CREATE trigger "BI_TEST" before insert on "TEST" for each row begin select "TEST_SEQ".nextval into :NEW.ID from dual; end;

    after i execute this command, i get the following exception:

    EOleException with message 'Parameter object is improperly defined. Inconsistent or incomplete information was provided'.

    as i understand ADOCommand still needs some parameters defined and i have no idea what parameters it would be. i don't have much experience with database programming so i just hope someone helps me to figure this one out thanks a lot.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't know Delphi so this will probably not help much.

    I'd remove all double quotes you use - they are absolutely unnecessary (unless you decided to create tables or sequences or ... using small letters (i.e. "table_1" and not TABLE_1, which is the default)).

    I'd also say that the "parameter object" refers to :NEW.ID (note the colon sign). If that's true, the next question is: how to fix it? No idea, I don't speak Delphi.

    Finally, why do you insist on creating a trigger using ADOCommand? What's wrong with creating all database objects using a simple SQL script and running it during the application setup process?

  3. #3
    Join Date
    Oct 2007
    Location
    Vilnius, Lithuania
    Posts
    10
    well, i'm writing an oracle client/explorer so it needs to have an ability to create a table. i'm actually not an expert of databases, i have only general knowledge. and it's my first app that works with oracle. i just use the 'database homepage' that oracle express edition provides me with. then in the object browser i create a table and it generates a sql for me. so i just refer on what the object browser gives me to generate my command in the app.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Whatever it is, Oracle is NOT happy with creating (and, probably dropping) tables dynamically. Create table(s) once and use them always. MS SQL Server does create temporary tables and drops them, but - this is Oracle we are talking about.

    Now, if you insist on "temporary" tables, Oracle provides so called "Global Temporary Tables" (search the documentation for more info). They are also created only once; data is stored temporarily and is deleted when the session ends; every session sees only its data (so, there might be millions of records in there that belong to one session and millions for another one, but every user will see only its own data); etc.

    I'd suggest you not to create tables on-the-fly. Not in Oracle.

  5. #5
    Join Date
    Oct 2007
    Location
    Vilnius, Lithuania
    Posts
    10
    thanks for you feedback. that's a bit strange to me. i think i don't know ANYTHING about oracle yet anyway, thank you for making it more clear. i'll have it in mind. but i don't think this error has something to do with oracle not liking to create tables dynamically. using the object browser creating a table is easy and i also used some third party software to try the sql statement. it worked. but i don't know why it doesn't through ADO. hope someone will help me on this one.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    One more thing (which will also be useless, but ...): try to terminate the CREATE TRIGGER string with a slash / sign. In SQL*Plus (tool which is used to communicate with an Oracle database), "simple" SQL statements are terminated with a semi-colon. PL/SQL blocks (which is a CREATE TRIGGER statement) must be terminated with a slash. It would look like this:
    Code:
     CREATE trigger BI_TEST before insert on TEST for each row begin select TEST_SEQ.nextval into :NEW.ID from dual; end;/
    I'm sorry for not being able to help you any better, but I really have no idea about tool you use.

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Littlefoot is right, the problem is the :NEW. ADO (and ODBC et al for that matter) use a : to signify a bind variable but clearly in this case :NEW isn't one.

    FYI I am a Delphi/Oracle developer and can say that in my [cough] too many years I have never created tables or triggers etc through a Delphi client.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  8. #8
    Join Date
    Oct 2007
    Location
    Vilnius, Lithuania
    Posts
    10
    so is there a way out of this situation or i should just drop this whole deal of creating that damn trigger? you see, my software isn't neither commercial, neither i will use it for some serious matters. it's just to show my lecturers that i can handle delphi+oracle

  9. #9
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    There are technical means to get around the problem, but I don't think you would want to.

    Generally, defining and building the schema is a one off event... just as is building/compiling your Delphi application. Ie, it is part of the development/install process not part of the application runtime. You would generally use SqlPlus or some other tool to build your schema, leaving your client as just that, a DML client. There are exceptions, but extremely rare.

    Modifying the schema structure at runtime will also cause you no end of other problems... packages going invalid, other connected clients suddenly finding their session state invalidated etc. I appreciate you're not working on an enterprise application right now, but I can see huge scalability problems of having a client executing runtime DDL.

    Also consider that it is poor design to have an application connect directly to the schema where its data resides, so executing any DDL in this manner should be impossible anyway.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  10. #10
    Join Date
    Oct 2007
    Location
    Vilnius, Lithuania
    Posts
    10
    thank you for lighting the path so as i understand now, the oracle database administration tool doesn't execute those SQL statements it shows me? it rebuilds the schema, does some more serious stuff and just gives me that simple statement? yes, i still have MUCH to learn. didn't think it goes that way so is it just for oracle or pretty much all of the databases work this way?

  11. #11
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    The Admin tool doesn't rebuild the schema it (should!) only execute the SQL you see. The effect is that of Oracle managing dependencies between objects. When an object definition changes, other objects which 'depend' on that original object are now in a suspect state because Oracle doesn't know how the dependent objects have been affected.

    Pseudo code....

    Create table a (columns b,c)
    Create trigger d on table a which does something with columns b,c

    All is fine.

    Drop column c from table a.
    Trigger d is now invalid - in this case it could never become valid.

    Any code trying to modify table a which causes the trigger to fire *will* fail and can never be executed/commited successfully.

    These are general database principles rather than an Oracle specific problem. In reality Oracle will try to recompile the trigger on the first execution... but now that object state has also changed and so other dependencies could be affected.....

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  12. #12
    Join Date
    Oct 2007
    Location
    Vilnius, Lithuania
    Posts
    10
    thank you very much. i appreciate the time you spend on guidance to noobs such as me

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •