Re: Executing SQL Statements throught ADO

"Jan Korecki" <Jan.Korecki_at_contactor.se> wrote in message news:3fcfedbb$0$53623$57c3e1d3_at_news3.bahnhof.se...
>
>
> Mark Filson wrote:
> > Hi All,
> >
> > Here's the issue I'm having... I have a VB COM component that is used to
> > parse SQL files and apply them to an instance of Oracle. Most of the
> > statements in the sql file work but a few don't. Here is an example of
an
> > offending one:
> >
> >
> > CREATE OR REPLACE TRIGGER TR_SEQ_APPLICATION_SETTINGS
> > AFTER INSERT OR UPDATE ON APPLICATION_SETTINGS
> > FOR EACH ROW
> > BEGIN
> > SELECT SEQ_APPLICATION_SETTINGS.nextval INTO :new.ID FROM dual;
> > END TR_SEQ_APPLICATION_SETTINGS;
> > /
> >
> >
> > The Sequence that's referenced is created successfully and my code says
that
> > the trigger is created (ie, no errors are thrown), but when I attempt to
> > insert a bunch of values into the table APPLICATION_SETTINGS, errors are
> > generated. Is there something about the syntax of this create statement
> > thats wrong. For what it's worth, the above statement can be run in SQL
> > PLUS successfully and the trigger is created.
> >
> > Cheers.
> >
> >
>
> Hi!
>
> Simpe test in sqlplus
>
>
> SQL> CREATE TABLE TEST5(
> 2 id NUMBER(10),
> 3 name VARCHAR(40));
>
> Table created.
>
> SQL>
> SQL>
> SQL> CREATE SEQUENCE test5_seq;
>
> Sequence created.
>
> SQL> CREATE OR REPLACE TRIGGER test5_trg
> 2 AFTER INSERT OR UPDATE ON TEST5
> 3 FOR EACH ROW
> 4 BEGIN
> 5 SELECT test5_seq.NEXTVAL INTO :NEW.ID FROM dual;
> 6 END test5_trg;
> 7 /
> CREATE OR REPLACE TRIGGER test5_trg
> *
> ERROR at line 1:
> ORA-04084: cannot change NEW values for this trigger type
>
>
>

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_76a.htm#2063897
> says:
> ...
> AFTER
> ...
> * You cannot specify an AFTER trigger on a view or an object view.
> * You cannot write either the :OLD or the :NEW value.
>
> ...
>
>
>
> SQL>
> SQL> CREATE OR REPLACE TRIGGER test5_trg
> 2 BEFORE INSERT ON TEST5
> 3 FOR EACH ROW
> 4 BEGIN
> 5 SELECT test5_seq.NEXTVAL INTO :NEW.id FROM dual ;
> 6 END Test5_seq;
> 7 /
>
> Trigger created.
>
>
>
> SQL> insert into test5 (name) values ('test');
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL>
>
>
> Note that i removed "or update" in
> > AFTER INSERT OR UPDATE ON APPLICATION_SETTINGS
>
> I dont think you want to alter the id everytime you update a record.
>
>
>
> Regards,
> Janne!
>
>
>
>
>
>
>
>
>
>
>
> > AFTER INSERT OR UPDATE ON APPLICATION_SETTINGS
>

Hi,

My example code above should have said "BEFORE INSERT". It appears as though this is an issue with the ORAOLEDB provider. For some reason the ":NEW" in the trigger is not correctly interpreted but no errors are displayed by ADO. Received on Thu Dec 04 2003 - 20:43:08 CST