Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 442 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
trigger

Hi All,
I am using
Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0
I am using Oracle HR schema
I am trying to learn triggers
Below is my table script.
CREATE TABLE FOO(
ID NUMBER PRIMARY KEY,
DATA VARCHAR2(100));
CREATE SEQUENCE FOO_SEQ
CREATE OR REPLACE TRIGGER BIFER_FOO_ID_PK
BEFORE INSERT
ON FOO
FOR EACH ROW
BEGIN
SELECT FOO_SEQ.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END;
INSERT INTO FOO (DATA) VALUES('CHRISTOMER');
INSERT INTO FOO(ID,DATA) VALUES(5,'SEAN')
SQL> SELECT * FROM FOO ;
ID DATA
---------- --------------------------------------------------------------------------------
1 CHRISTOMER
2 SEAN
I want to understand this behaviour,
the sequence will automaticaly insert into FOO, if we don't give the ID column,
But giving the ID column also, it inserts by sequence.
Please help me understand the behaviour
Thanks,
Best Answer
-
Hi,
2947022 wrote: Hi All, I am using Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0 I am using Oracle HR schema I am trying to learn triggers Below is my table script. CREATE TABLE FOO( ID NUMBER PRIMARY KEY, DATA VARCHAR2(100)); CREATE SEQUENCE FOO_SEQ CREATE OR REPLACE TRIGGER BIFER_FOO_ID_PK BEFORE INSERT ON FOO FOR EACH ROW BEGIN SELECT FOO_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL; END; INSERT INTO FOO (DATA) VALUES('CHRISTOMER'); INSERT INTO FOO(ID,DATA) VALUES(5,'SEAN') SQL> SELECT * FROM FOO ; ID DATA ---------- -------------------------------------------------------------------------------- 1 CHRISTOMER 2 SEAN I want to understand this behaviour, the sequence will automaticaly insert into FOO, if we don't give the ID column, But giving the ID column also, it inserts by sequence. ...
Exactly! The trigger is always setting :NEW.id, regardless of whether it was included in the INSERT statement or not. The trigger isn't doing any tests, or any branching; it does exactly the same thing any time a row is INSERTed, no matter what columns were specified in that INSERT statement, or what values were given.
By the way, starting in Oracle 11.1, you don't need to SELECT sequence values from dual (or any other table); you can use sequence_name.NEXTVAL in most places where an expression is allowed. For example:
CREATE OR REPLACE TRIGGER bifer_foo_id_pk BEFORE INSERT ON foo FOR EACH ROW BEGIN :NEW.id := NVL ( :NEW.id , foo_seq.NEXTVAL ); END bifer_foo_id_pk; /
Answers
-
You told your trigger to use the sequence regardless. Perhaps you want your trigger to check if :new.id is null then...
-
Hope I'm not doing your homework You have to understand what your trigger does. It replaces the value of the ID-column no matter what was submitted.
-
Hi,
2947022 wrote: Hi All, I am using Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0 I am using Oracle HR schema I am trying to learn triggers Below is my table script. CREATE TABLE FOO( ID NUMBER PRIMARY KEY, DATA VARCHAR2(100)); CREATE SEQUENCE FOO_SEQ CREATE OR REPLACE TRIGGER BIFER_FOO_ID_PK BEFORE INSERT ON FOO FOR EACH ROW BEGIN SELECT FOO_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL; END; INSERT INTO FOO (DATA) VALUES('CHRISTOMER'); INSERT INTO FOO(ID,DATA) VALUES(5,'SEAN') SQL> SELECT * FROM FOO ; ID DATA ---------- -------------------------------------------------------------------------------- 1 CHRISTOMER 2 SEAN I want to understand this behaviour, the sequence will automaticaly insert into FOO, if we don't give the ID column, But giving the ID column also, it inserts by sequence. ...
Exactly! The trigger is always setting :NEW.id, regardless of whether it was included in the INSERT statement or not. The trigger isn't doing any tests, or any branching; it does exactly the same thing any time a row is INSERTed, no matter what columns were specified in that INSERT statement, or what values were given.
By the way, starting in Oracle 11.1, you don't need to SELECT sequence values from dual (or any other table); you can use sequence_name.NEXTVAL in most places where an expression is allowed. For example:
CREATE OR REPLACE TRIGGER bifer_foo_id_pk BEFORE INSERT ON foo FOR EACH ROW BEGIN :NEW.id := NVL ( :NEW.id , foo_seq.NEXTVAL ); END bifer_foo_id_pk; /