Need help with simple Oracle-SQL-script (Trigger and User)

Hello,

I'm new to Oracle and need some sql-statements to create a table with an trigger. This is the code I use now:

CREATE TABLE DMS.Types(

    ID NUMBER(5) NOT NULL,
    NAME VARCHAR2(100) NOT NULL,
    FLAGS VARCHAR2(30) NOT NULL,
    DATECREATED DATE NOT NULL,
    DATEMODIFIED DATE NOT NULL,
    CREATEDBY VARCHAR2(100) NOT NULL,
    MODIFIEDBY VARCHAR2(100) NOT NULL,
    ATTRIBUTES VARCHAR2(3000) NOT NULL,
    USERSALLOWED VARCHAR2(2000) NOT NULL,     DEFAULTCOLLECTION NUMBER(4) NOT NULL,     ICONID NUMBER(3) NOT NULL,
    SCRIPT VARCHAR2(4000) NOT NULL,
    SCRIPT2 VARCHAR2(4000) NOT NULL,
    CONSTRAINT TypesConstID UNIQUE(ID))

Then I use this:

CREATE SEQUENCE TYPES_SEQ START WITH 1 INCREMENT BY 1 And this:

CREATE TRIGGER TYPES_TRIGID
BEFORE INSERT ON DMS.Types
FOR EACH ROW
BEGIN
NEW.ID = TYPES_SEQ.NEXTVAL
END When I try to insert a new row into types then i get an error ORA-04098: Trigger DMS.TYPES_TRIGID ist ungültig oder konnte nicht bestätigt werden. (Sorry, in german).

What is wrong? And how is the statement to set one scheme as default for one user? I don't want to use DMS. in front of every statement. Wehn im logged in with a special user I think that everything shoul affect a specified scheme and tablespace.

Sorry for bad english.

Volker Received on Fri Oct 18 2002 - 03:42:31 CDT