Re: equivalent of identity(MS-SQL2000) in oracle

For the Identity, one of solution is using trigger and sequence.

Example :

CREATE SEQUENCE "SCOTT"."SEQEMPNOKEY" INCREMENT BY 1 START WITH 1 MAXVALUE     1.0E28 MINVALUE 1 NOCYCLE
    CACHE 20 NOORDER;
GRANT SELECT ON "SCOTT"."SEQEMPNOKEY" TO PUBLIC; GRANT ALTER ON "SCOTT"."SEQEMPNOKEY" TO PUBLIC; CREATE OR REPLACE TRIGGER "SCOTT"."EMPNO_IDENTITY" BEFORE     INSERT
    ON "SCOTT"."employees"
    FOR EACH ROW
    DECLARE
dummy number(38,0);
BEGIN
select SEQEMPNOKEY.nextval into dummy from dual; :new.Empno :=dummy;
END; <iluzn_at_my-deja.com> wrote in message news:940727$gmp$1_at_nnrp1.deja.com...
> i am in the proccess of converting my database routines to suit an
> oracle database. The old DBMS used is microsoft sql server 2000.
> the statement identity is used to automatically increment a column
> sequencially so that the field is kept unique.
>
> CREATE TABLE employees
> (Empno INT IDENTITY,
> firstName VARCHAR(30),
> lastName VARCHAR(30))
>
> to then add a record you need only specify the other fields
>
> INSERT INTO employees(firstName,lastName) VALUES ('Bob', 'Smith')
> INSERT INTO employees(firstName,lastName) VALUES ('Fred', 'Green')
>
> the data then becomes
>
> Empno firstName lastName
> ----------------------------------
> 1 Bob Smith
> 2 Fred Green
>
> could anyone help me on duplicating a similar increment on oracle?
>
> also...
>
> CREATE TABLE Client
> (ClientID int IDENTITY primary key clustered,
> firstName varchar(64) NOT NULL,
> lastName VarChar(64) NOT NULL,
> nameID int NOT NULL)
>
> could someone tell me what the clustered keyword is and how to
> duplicate its effects on oracle or whether they are automatically done
> for primary keys within oracle.
>
> if you could help me with any of this i would be incredibly gratefull
>
> -=-=-
> ILUZN
> -=-=-
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Tue Jan 16 2001 - 04:09:02 CST