Newbie: Trigger compilation error

From: art <art_teqNOSPAM_at_hotmail.com>
Date: Wed, 13 Mar 2002 10:17:04 -0000
Message-ID: <3c8f2720$1_1@nnrp1.news.uk.psi.net>

We have two ORACLE 8 database services each on different servers (W2K and NT). The following SQL works on the NT one with no warnings, but complains 'Warning: Trigger created with compilation errors.' on the other. I have replaced the 'SELECT CDHlpDskLogin.S_MYDATA.nextval INTO :new.USERNUMBER' with a simple insert instead, and it works. If I run 'SELECT CDHlpDskLogin.S_MYDATA.nextval FROM dual;' then that works too.

I am very confused. Why won't the trigger compile properly? Any help much appreciated.
Cheers,
.

SQL : - CREATE TABLE CDHlpDskLogin.MYDATA
(
USERNUMBER NUMBER (10,0)
) TABLESPACE CDHLPDSK; CREATE SEQUENCE CDHlpDskLogin.S_MYDATA START WITH 1;

CREATE OR REPLACE TRIGGER CDHlpDskLogin.TR_MYDATA BEFORE INSERT ON CDHlpDskLogin.MYDATA FOR EACH ROW BEGIN
SELECT CDHlpDskLogin.S_MYDATA.nextval INTO :new.USERNUMBER FROM dual;
END; Received on Wed Mar 13 2002 - 04:17:04 CST