asked on

Trigger/Function creation in Oracle regarding

COLUMN1        COLUMN2
8983888 2
8983888        1
8983888        5

Need to sort this first

8983888        1
8983888 2
8983888        5

then I need to sequence COLUMN2

8983888        1
8983888        2
8983888        3

How to do this using a trigger(if possible)?

I am asking about a trigger because they will be inserted(using SQL Loader) and as soon as they are inserted, the tables should be structured according to the above requirement.

If not possible how to do that using a function? ( or ) a sql query?

Thanks in anticipation.

jamesgu

so the sequence of a new record is always incremented by 1?

you may use a sequence

CREATE SEQUENCE your_sequence
INCREMENT BY 1
START WITH 1

CREATE OR REPLACE TRIGGER tri_your_table
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
      SELECT your_sequence.NEXTVAL
      INTO :new.sequence_column
      FROM dual;
END;

srikanthradix

ASKER

Suppose the data is like this:

COLUMN1 COLUMN2
8983888 2
8983888 1
8983888 5
8983889 4
8983889 1
8983889 5

The Result should be

COLUMN1 COLUMN2
8983888 1
8983888 2
8983888 3
8983889 1
8983889 2
8983889 3

Sorry, I am not being clear.

ASKER CERTIFIED SOLUTION