{"id":1434,"date":"2015-11-15T14:50:27","date_gmt":"2015-11-15T11:50:27","guid":{"rendered":"http:\/\/www.fatihacar.com\/blog\/?p=1434"},"modified":"2015-12-04T15:13:01","modified_gmt":"2015-12-04T12:13:01","slug":"auto-increment-primary-key-with-trigger-in-oracle-11g","status":"publish","type":"post","link":"http:\/\/www.fatihacar.com\/blog\/auto-increment-primary-key-with-trigger-in-oracle-11g\/","title":{"rendered":"Auto Increment Primary Key With Trigger in Oracle 11g"},"content":{"rendered":"

You can provide auto increment primary with trigger by appling below operations. <\/p>\n

Create Table<\/strong><\/p>\n

\n

SQL> CREATE TABLE TESTUSER.TBL_TEST
\n(id number primary key,
\nname varchar2(100));<\/p><\/blockquote>\n

Create Sequence<\/strong><\/p>\n

\n

SQL> CREATE SEQUENCE TESTUSER.SEQ_TEST_ID
\n START WITH 1
\n MAXVALUE 999999999
\n MINVALUE 1
\n CYCLE
\n NOCACHE
\n NOORDER;<\/p><\/blockquote>\n

Create Trigger For Auto Increment<\/strong><\/p>\n

\n

SQL> CREATE OR REPLACE TRIGGER TESTUSER.TRG_AUTO_INC_TEST_ID
\n before insert on TESTUSER.TBL_TEST
\n for each row
\nbegin
\n if inserting then
\n if :NEW.”ID” is null then
\n select TESTUSER.SEQ_TEST_ID.nextval into :NEW.ID from dual;
\n end if;
\n end if;
\nend;
\n\/\n<\/p><\/blockquote>\n

Oracle 12c release has primary key auto increment feature with identity type column. You can use like below.
\n<\/p>\n

Create Table With Identity Column<\/strong><\/p>\n

\n

SQL> CREATE TABLE TESTUSER.TBL_TEST12C (
\n id number generated always as identity,
\n name varchar2(100)
\n);\n<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

You can provide auto increment primary with trigger by appling below operations. Create Table SQL> CREATE TABLE TESTUSER.TBL_TEST (id number primary key, name varchar2(100)); Create Sequence SQL> CREATE SEQUENCE TESTUSER.SEQ_TEST_ID START WITH 1 MAXVALUE 999999999 MINVALUE 1 CYCLE NOCACHE NOORDER; Create Trigger For Auto Increment SQL> CREATE OR REPLACE TRIGGER TESTUSER.TRG_AUTO_INC_TEST_ID before insert on TESTUSER.TBL_TEST…<\/p>\n","protected":false},"author":37,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[9,12,6],"tags":[14,90,32,103],"class_list":["post-1434","post","type-post","status-publish","format-standard","hentry","category-administration","category-oracle-procedure","category-oracle-sql","tag-database-administration","tag-oracle","tag-oracle-administration","tag-oracle-sql-query"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p39NFI-n8","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/1434","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/users\/37"}],"replies":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/comments?post=1434"}],"version-history":[{"count":2,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/1434\/revisions"}],"predecessor-version":[{"id":1436,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/1434\/revisions\/1436"}],"wp:attachment":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/media?parent=1434"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/categories?post=1434"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/tags?post=1434"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}