CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
Jaav
Jaav

Reputation: 37

Separate incrementing trigger for every row in the table

I'm having trouble creating incrementing trigger for every row in the table. What I need is to numerate rows dependent on one of the columns. For example:

     table
column1|column2
    1  |  1
    1  |  2
    2  |  1
    1  |  3
    3  |  1
    2  |  2

I've created sequence:

CREATE SEQUENCE inc_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1;

And trigger:

CREATE OR REPLACE TRIGGER inc_on_insert
BEFORE INSERT ON table
FOR EACH ROW
BEGIN
SELECT inc_seq.nextval
INTO :new.column2
FROM dual;
END;

What I get now are values incremented, but increment continues for every row, and doesent reset. I don't have clue how to create statement to start from 1 on every different value for column1.

Edit:

CREATE TABLE moves (
    move_id        NUMBER,
    game_id NOT NULL
        REFERENCES games ( game_id )
            ON DELETE CASCADE,
    move_number    NUMBER NOT NULL,
    stages_count   NUMBER DEFAULT 1,
    CONSTRAINT move_pk PRIMARY KEY ( move_id ),
    CONSTRAINT moves_const_1 UNIQUE ( game_id,
                                      move_number,
                                      stages_count )
);

Upvotes: 0

Views: 398

Answers (3)

wolφi
wolφi

Reputation: 8361

I second Kaushik's opinion and wouldn't use a trigger for that purpose. For instance, what happens if a row gets deleted?

If you insist, however, I'd code it like that:

CREATE TABLE mytable (column1 NUMBER, column2 NUMBER);

CREATE OR REPLACE TRIGGER inc_on_insert
BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
  SELECT COUNT(*)+1 INTO :NEW.column2 
    FROM mytable 
   WHERE column1=:NEW.column1;
END;
/

INSERT INTO mytable(column1) VALUES (5);
SELECT * FROM mytable;
5 1

INSERT INTO mytable(column1) VALUES (5);
SELECT * FROM mytable;
5 1
5 2

INSERT INTO mytable(column1) VALUES (6);
SELECT * FROM mytable;
5 1
5 2
6 1

INSERT INTO MYTABLE(COLUMN1) VALUES (5);
SELECT * FROM mytable;
5 1
5 2
6 1
5 3

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

Trigger is not ideal for such purposes. I would rather create a view using row_number(). You may still use your trigger/sequence to generate an id column for the table.

CREATE
    OR replace VIEW t_view AS
SELECT column1
    ,row_number() OVER (
        PARTITION BY column1 ORDER BY id --id generated using your trigger
        ) AS column2
FROM t
ORDER BY id;

Demo

select * from t_view;

| COLUMN1 | COLUMN2 |
|---------|---------|
|       1 |       1 |
|       1 |       2 |
|       2 |       1 |
|       1 |       3 |
|       3 |       1 |
|       2 |       2 |

Upvotes: 4

Vladimir.V.Bvn
Vladimir.V.Bvn

Reputation: 1118

Please try something like:

CREATE OR REPLACE TRIGGER inc_on_insert
BEFORE INSERT ON table
FOR EACH ROW

BEGIN
select count(1) into thereiscolumn1 from table where column1 = :new.column1 and rownum=1;
if thereiscolumn1 > 0 THEN
    SELECT (MAX(column2) + 1 ) INTO :new.column2 from table WHERE column1 = :new.column1
ELSE
    SELECT 1 INTO :new.column2 FROM dual;
END IF;

END;

Upvotes: 0

Related Questions