Bind variable in trigger's body

Question

I'm learning Oracle db and encountered on a problem with using bind variables in triggers body.

Here I created a bind variable with VARIABLE command

sqlplus:

SQL> VARIABLE my_variable number;
SQL> begin
  2  :my_variable := 0;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> print :my_variable;

MY_VARIABLE
-----------
          0

(variable has been successfully created)

After this I tried to create a trigger that would increment the variable on each insert, but for some reason I'm keeping getting next warning: Trigger created with compilation errors.

sqlplus:

SQL> create or replace trigger my_trigger before insert on my_table
  2  for each row
  3  begin 
  4  :my_variable := :my_variable + 1; 
  5  end;
  6  /

Warning: Trigger created with compilation errors.

What am I doing wrong?

Thank you for any help!

Answer 1

As commented, that won't work that way; if you want to auto-increment column's value, either use

• sequence (while inserting),
• trigger (that uses that sequence so that you wouldn't have to use it in insert statement)
• identity column (if your database version supports it; Oracle 11g does not)

---

Anyway: a workaround, if you're interested in it.

Package specification that contains your variable:

SQL> create or replace package pkg_bind as
  2    my_variable number := 0;
  3  end pkg_bind;
  4  /

Package created.

Table, whose ID column is supposed to be populated with it:

SQL> create table my_table
  2    (id     number,
  3     name   varchar2(20));

Table created.

Trigger, which sets package variable's value and uses it for ID:

SQL> create or replace trigger my_trigger
  2    before insert on my_table
  3    for each row
  4  begin
  5    pkg_bind.my_variable := pkg_bind.my_variable + 1;
  6    :new.id := pkg_bind.my_variable;
  7  end;
  8  /

Trigger created.

Let's test it:

SQL> insert into my_table (name) values ('Little');

1 row created.

OK; ID is set:

SQL> select * from my_table;

        ID NAME
---------- --------------------
         1 Little

Now, if you want, you can use variable, put package's variable into it (can't tell what benefit would that make, but - you can do it):

SQL> var my_variable number
SQL> exec :my_variable := pkg_bind.my_variable;

PL/SQL procedure successfully completed.

SQL> print my_variable

MY_VARIABLE
-----------
          1

SQL>

---

Usual approach (on Oracle 11g):

SQL> create table my_table
  2    (id     number,
  3     name   varchar2(20));

Table created.

SQL> create sequence my_seq;

Sequence created.

SQL> create or replace trigger my_trigger
  2    before insert on my_table
  3    for each row
  4  begin
  5    :new.id := my_seq.nextval;
  6  end;
  7  /

Trigger created.

SQL> insert into my_table (name) values ('Foot');

1 row created.

SQL> select * from my_table;

        ID NAME
---------- --------------------
         1 Foot

SQL>