Forum Stats

  • 3,827,406 Users
  • 2,260,771 Discussions
  • 7,897,227 Comments

Discussions

trigger

2947022
2947022 Member Posts: 59
edited May 22, 2015 3:40PM in SQL & PL/SQL

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,

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,070 Red Diamond
    edited May 22, 2015 3:40PM 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;
    /
    
    
    2947022

Answers

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 22, 2015 2:02PM

    You told your trigger to use the sequence regardless. Perhaps you want your trigger to check if :new.id is null then...

    2947022
  • Oyvind Isene
    Oyvind Isene Member Posts: 64 Bronze Badge
    edited May 22, 2015 2:07PM

    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.

    2947022
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,070 Red Diamond
    edited May 22, 2015 3:40PM 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;
    /
    
    
    2947022
This discussion has been closed.