Creating a trigger in Oracle Express

Question

I was trying to do something like auto-increment in Oracle 11g Express and SQL Developer. I know very little about Oracle and I am also new to triggers.

I tried running this, but I don't know how to do it properly.

CREATE TABLE theschema.thetable (id NUMBER PRIMARY KEY, name VARCHAR2(30));  CREATE SEQUENCE theschema.test1_sequence START WITH 1 INCREMENT BY 1;  create or replace trigger insert_nums before insert on theschema.thetable for each row begin select test1_sequence.nextval into :new.id from dual; end; / 

When I try to create the trigger, I get a screen which asks me for some "binds". The dialog box has only one check box "null". What does this mean and how do I make a script that works properly?

Any precautions to take while doing this kind of "auto-increment" ?

Answer 1

It seems that SQL Developer thinks that you are running a plain DML (data manipulation) script, not a DDL (data definition). It also thinks that :new.id is a bindable variable.

Why this happens, I don't know; I can't reproduce it in Oracle SQL Developer 2.1.

Try to open a new SQL worksheet window in the theschema schema and execute a "whole" script (not a statement) by pressing F5 (not F9).

Answer 2

This is how I have solved this problem, put "set define off;" before the command:

set define off; create or replace trigger [...] [...] end; / 

Then highlight both commands and press F9 to run. Or you could run all the commands with F5.

It seems, that if the commands are executed separetly with F9, then the set define off does not take affect.