Oracle: auto-increment trigger.

Question

Found the following trigger in a database which was blocking inserts on a unique varchar primary key

CREATE OR REPLACE TRIGGER  "BI_PRIVILEGE" 
  before insert on "PRIVILEGE"               
  for each row  
begin   
  if :NEW."PRIVILEGE-ID" is null then 
    select "PRIVILEGE_SEQ".nextval into :NEW."PRIVILEGE-ID" from dual; 
  end if; 
end; 

Is this an auto-number generator? I can easily disable it to fix my problem, but will there be unforseen negative ramifcations for the primary key?

I have actually been looking for code to set up auto-increment triggers for primary keys and could use this as a template if this is what it is doing. If it is, it is likely doing it incorrectly as the primary key is specifically PRIVILEGE_ID not PRIVILEGE-ID, also, shouldn't some sort of application_error arise in the case of conflicts, etc?

Answer 1

Okay, I think I get what's going on. The answer to your question is an absolutely massive yes. There can be a large impact if you disable this trigger.

The reason this trigger seems to exist is to deal with the situation where a primary key value is not provided on an insert into your table. If this occurs anywhere in your code them removing the trigger will break those inserts.

You have to do two things.

1. Correct the trigger, it's obviously broken; fix it:

   CREATE OR REPLACE TRIGGER  BI_PRIVILEGE
     before insert on PRIVILEGE              
     for each row  
   begin   
     if :NEW.PRIVILEGE_ID is null then 
       select PRIVILEGE_SEQ.nextval into :NEW.PRIVILEGE_ID from dual; 
     end if; 
   end; 
   

   If you're using Oracle 11G or greater then you can use this instead:

     if :NEW.PRIVILEGE_ID is null then 
       :NEW.PRIVILEGE_ID := PRIVILEGE_SEQ.nextval; 
     end if; 
   

2. Work out whether this actually happens. If you do insert records without a primary key you need to find out why this happening and whether the behaviour is correct. If it is you're stuck with the trigger, otherwise fix that. If you never insert records without a primary key then you can disable the trigger.

   The quickest way of finding out may be to disable the trigger anyway but it would break your inserts. If this is a production database only you can tell whether it's worth it. I wouldn't personally.