before insert trigger 1

[mufka]

I'm experimenting with sequences and triggers. I've created a sequence called AUTONUM, a table named EMP (columns EMP_ID, EMP_NAME, EMP_DEPT) and the following trigger.

CREATE OR REPLACE TRIGGER  "BI_EMP" 
  before insert on "EMP"               
  for each row  
begin   
    select "AUTONUM".nextval into :NEW.EMP_ID from dual; 
end;

My impression would be that this would mean that I could use an insert like:

INSERT INTO EMP VALUES ('John', 'Sales');

But it is looking for the EMP_ID field to be filed. Isn't that what the trigger does?

 

[SantaMufasa]

Mufka,

If you do not tell Oracle otherwise, it expects to populate all columns of your table with the VALUES clause of your INSERT statement.

To instruct Oracle to not to look for the EMP_ID in the list of expressions in your VALUES clause, you can say:

INSERT INTO EMP [B][I](EMP_NAME, EMP_DEPT)[/I][/B] VALUES ('John', 'Sales');

...then your code should work.

Please let us know the outcome.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[mufka]

Of course. Whenever I try something new, the basics escape me for some reason.