trigger

2947022

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,

Frank Kulash

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;
/

John Stegeman

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

Oyvind Isene

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.

Frank Kulash

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;
/