Re: Executing SQL Statements throught ADO

From: Jan Korecki <Jan.Korecki_at_contactor.se>
Date: Fri, 05 Dec 2003 03:29:19 +0100
Message-ID: <3fcfedbb$0$53623$57c3e1d3@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 Received on Thu Dec 04 2003 - 20:29:19 CST