CODEWITHSUNDEEP
sqldatabaseoracle-databasetriggers
Josh123
Josh123

Reputation: 159

Autopopulating a column with a Trigger SQL

I'm trying to start a sequence that automatically populates a column when an insertion has been made. It should start from 500 and increment by 1. Any idea how would I go about doing this? So far I have something like this, but it seems to crash

CREATE TRIGGER ADD_TRIGGER ON tablename
AFTER INSERT
AS
BEGIN
    ADD colname NUMBER IDENTITY(500,1);
END
GO

Upvotes: 1

Views: 97

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49102

Oracle 12c introduces IDENTITY COLUMNS.

SQL> CREATE TABLE new_identity_table
  2    (
  3      ID   NUMBER GENERATED ALWAYS AS IDENTITY,
  4      text VARCHAR2(50)
  5    );

Table created.

SQL>
SQL> INSERT
  2  INTO new_identity_table
  3    (
  4      text
  5    )
  6    VALUES
  7    (
  8      'This table has an identity column'
  9    );

1 row created.

SQL> column text format A40;
SQL>
SQL> select * from new_identity_table;

        ID TEXT
---------- ----------------------------------------
         1 This table has an identity column

Oracle creates a sequence to populate the identity column. You can find it named as ISEQ$$

SQL> select sequence_name, min_value, max_value, increment_by from user_sequences;

SEQUENCE_NAME         MIN_VALUE  MAX_VALUE                     INCREMENT_BY
-------------------- ---------- ----------------------------   ------------
ISEQ$$_93199                  1 9999999999999999999999999999   1

More more information about the identity columns, use the ALL_TAB_IDENTITY_COLS view.

SQL> SELECT table_name,
  2         column_name,
  3         generation_type,
  4         identity_options
  5  FROM   all_tab_identity_cols
  6  WHERE  owner = 'LALIT'
  7  ORDER BY 1, 2;

TABLE_NAME           COLUMN_NAME     GENERATION IDENTITY_OPTIONS
-------------------- --------------- ---------- --------------------------------------------------
NEW_IDENTITY_TABLE   ID              ALWAYS     START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999
                                                999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N
                                                , CACHE_SIZE: 20, ORDER_FLAG: N

For pre 12c releases, see Auto-increment primary key in Pre 12c releases (Identity functionality)

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172578

You can create a sequence

CREATE SEQUENCE mySeq
  START WITH 500
  INCREMENT BY 1
  CACHE 100;

and then use it in your trigger

CREATE OR REPLACE TRIGGER myTrigger
  AFTER INSERT ON table_name
  FOR EACH ROW
BEGIN
  SELECT mySeq.nextval
    INTO :new.colname 
    FROM dual;
END;

Upvotes: 1

Related Questions