Please, help me!
I am a student in Technical University in Plovdiv, Bulgaria. I must finish my graduation work very soon. For the purpose I must create trigger(actually a few triggers) at run time. For my graduation work I use Borland C++ Builder 6. I use BDE palette. I try to create trigger with Query component and her property SQL.
Here is my code:

CreateTrigger->Close();
        CreateTrigger->SQL->Clear();
        CreateTrigger->SQL->Add("CREATE TRIGGER test_trg BEFORE INSERT ON test");
        CreateTrigger->SQL->Add("FOR EACH ROW BEGIN SELECT test_seq.NEXTVAL");
        CreateTrigger->SQL->Add("INTO :NEW.id FROM dual;");
        CreateTrigger->SQL->Add("dbms_output.put_line(:NEW.id); END test_trg;");
        CreateTrigger->ExecSQL();

This is my test. In my graduation work the name of the trigger and the name of the sequence will be fetch from Edit Boxes. That is the reason to use Add instead of put a static SQL code in SQL property.
The error that I reseve is:
Project TriggerBeforInsert.exe raised exception class EDatabaseError with message 'Create Trigger: Field 'NEW.id' is of an unknown type'. Process stopped. Use Step or Run to continue.
I think that Borland think that :New.id is a Parameter and expects to have value.
I search in internet a lot but i can't find a decision.
Please help me!
Thanks in advanced.

Hi

Possibly you can start designing your triggers with oracle sql developer until they function properly. Then you can insert the final result in your Borland c++ program working together with BDE. By Acting this way you will feel certain that arising problems are only localized at turbo c++ or more likely at BDE.

Note btw, because this is a trigger fired every time a new record is inserted in table test which should insert a new generated sequence number, why not simply insert the sequence number in the original insert statement like in this way:

insert into test (id, ...) values (test_seq.NEXTVAL, ...);

which would do exactely the same task?

Note btw2: there is also a special Oracle database forum here, what seems to be a better place to post questions regarding oracle DB.

-- tesu

Thanks but this solution is not a solution for me. I know that I can do this but I expect that the data at this table can be modified not only from my application. It is possible that some user insert data through MSQuery for example and then what will happen with my data. This is the reason that I want to create trigger.
Solution: After 1000 test I finally found that it work if I write "::" instead of ":".
I was right. Borland think that :New.id is parameter. I see that in property Params.

Thanks again anyway. :) I hope that this post will help to someone else. :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.