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?

You've identified the problem, the column name is incorrect. Why are you thinking about disabling the trigger (which is an auto-increment)? Why don't you just correct the bad code? — Ben, May 05 '13 at 20:18
@Ben because in this case the primary key is meant for unique strings. I cant imagine that auto-increment could be altogether useful in that context. — Stumbler, May 05 '13 at 20:24
If this trigger was blocking inserts then it certainly is being used, so disabling it might not be the best plan. Either way, It's worth thinking about whether there are any transactional problems going on in your database if this trigger can block application code. — Ben, May 05 '13 at 22:37

score 4 · Accepted Answer · answered May 05 '13 at 20:34

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.

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;

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.

Wouldn't 'NOT NULLABLE' be sufficient to guarantee that the primary key be always filled along with any other data? — Stumbler, May 05 '13 at 20:38
A primary key is always declared as `NOT NULL` @Duncan. That's not the problem; yes you ensure the primary key is always filled in but the trigger is dealing with those cases where it's _not_ filled in and automatically completing it for you before the not null constraint is tested. You could check if there's a single number in your column. If there is then the trigger may have been used before. — Ben, May 05 '13 at 20:40

score 1 · Answer 2 · edited May 23 '17 at 12:15

This script will get a value from a sequence and place it into a newly inserted row in the table. It is acting very similar to an autonumber. I assume that application code relies upon this trigger to populate the primary key for inserted rows, I would not recommend removing it before evaluating an application's source code.

Unless this was some forgotten about experiment, it is likely the application code depends on this trigger/sequence.

This is an acceptable solution for autoincrement functionality. See: How to create id with AUTO_INCREMENT on Oracle?

score 1 · Answer 3 · answered May 06 '13 at 06:32

" in this case the primary key is meant for unique strings. I cant imagine that auto-increment could be altogether useful in that context. "

If you mean that "PRIVILEGE-ID" is a varchar2 column then you're sort of correct. On the one hand, a number can also be a string, so it would work as a key. But if the key is supposed to have a specific format of letters and numbers, a monotonically incrementing number won't fit the patten.

For me, the concern would be that IF statement. It suggests that sometimes the key is populated by the application and at other times it is defaulted by the database. That is messy. Apart from anything else, having two sources of keys means that the sequence is no longer guaranteed to be unique. You now have the possibility that the sequence generated nextval will collide with a previous manually assigned number.

What to do?

If you have a development environment with good coverage from automated unit or integration tests, the answer is simple: disable the trigger, run your test suites and see what fails. If that doesn't describe your set-up (and I have the feeling it doesn't) then disabling the that trigger is riskier, because you can't have confidence that you have tested all the paths which might populate the table.

Mohammad Ali Abdullah · Answer 4 · 2022-07-19T05:54:34.567

Creating a Sequence:

CREATE SEQUENCE SEQ_CM_LC_FINAL_STATUS
MINVALUE 1 MAXVALUE 999999999999999999999999999 
INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

Adding a Trigger

CREATE OR REPLACE TRIGGER CM_LC_FINAL_STATUS_TRIGGER
BEFORE INSERT
ON CM_LC_FINAL_STATUS
FOR EACH ROW
BEGIN
:NEW.LC_FINAL_STATUS_NO := SEQ_CM_LC_FINAL_STATUS.NEXTVAL;
END;

The first step is to create a SEQUENCE in your database, which is a data object that multiple users can access to automatically generate incremented values. As discussed in the documentation, a sequence in Oracle prevents duplicate values from being created simultaneously because multiple users are effectively forced to “take turns” before each sequential item is generated. –

Finally, we’ll create our SEQUENCE that will be utilized later to actually generate the unique, auto incremented value. –

While we have our table created and ready to go, our sequence is thus far just sitting there but never being put to use. This is where TRIGGERS come in. Similar to an event in modern programming languages, a TRIGGER in Oracle is a stored procedure that is executed when a particular event occurs. Typically a TRIGGER will be configured to fire when a table is updated or a record is deleted, providing a bit of cleanup when necessary. –

In our case, we want to execute our TRIGGER prior to INSERT into our CM_LC_FINAL_STATUS table, ensuring our SEQUENCE is incremented and that new value is passed onto our primary key column.

Oracle: auto-increment trigger.

4 Answers4

Linked