I am stuck on a simple use of making a TRIGGER that will fire when adding new rows to a table and wanting SQL Developer to add the primary key value.
I seem to be able to make the trigger but it is not inserting the primary key but giving SQL Error: ORA-00947: not enough values.
Here is what I have.
CREATE TABLE HF_fishers
(
contact_id INT NOT NULL
,first_name VARCHAR(25)
,last_name VARCHAR(35)
,PRIMARY KEY (contact_id)
);
CREATE SEQUENCE HF_fishers_seq;
Then I have ran the following trigger:
CREATE
TRIGGER HF_fishers_seq_trigger
BEFORE INSERT
ON HF_fishers
FOR EACH ROW
BEGIN
IF (:new.contact_id IS NULL)
THEN
:NEW.contact_id := NVL ( :NEW.contact_id
, HF_fishers_seq.NEXTVAL
);
END IF;
END HF_fishers_seq_trigger;
=============
Then I run the following SQL statement that gives the error.
INSERT INTO HF_fishers VALUES('Jacob','Muller');
But if I do following all is good except it defeats the purpose of the trigger.
INSERT INTO HF_fishers VALUES(1,'Jacob','Muller');
MySQL has a nice command 'AUTO_INCREMENT' but from what I gather in Oracle I have to set up a trigger to automatically increment a column/cell which in this case I am using for the PRIMARY KEY.