After insert auto increment trigger

Question

The problem here is that, we use an application called ArcGIS that creates an upper management layer for our database, and when our users use ArcGIS to create a new object in the database, it adds a default value (0) to the primary key, so when the second object is created it triggers an ORA error for having duplicate values.

So my idea was to create an auto increment trigger for our PK but use AFTER INSERT instead. I couldn't find any example online for this specific case and simply switching BEFORE to AFTER gives an error saying that you can't use NEW with AFTER

SQL code of what I tried (taken from other questions):

CREATE OR REPLACE TRIGGER  "IMOVEL_TRIGGER" 
  after insert on IMOVEL               
  for each row  
begin   
  select IMOVEL_SEQ.nextval into :NEW.GEOCODIGO_IMOVEL from dual; 
end; 

It cant be a BEFORE INSERT trigger, because the application overwrites it

Simplifying, what I need is a AFTER INSERT trigger, that updates the PK to the sequence .nextval, it doesn't let me use :OLD or :NEW, so I'm not sure what must be done. Or an update trigger that only runs after it is created

This is pretty new territory for me, having to learn SQL now just to solve this issue

Answer 1

You can change :NEW values only in a BEFORE trigger. By the time you reach the AFTER trigger, the row has already been inserted, so it's too late to change the columns.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7004.htm says:

Restrictions on BEFORE Triggers

BEFORE triggers are subject to the following restrictions:

• You cannot specify a BEFORE trigger on a view or an object view.

• You can write to the :NEW value but not to the :OLD value.

Restrictions on AFTER Triggers

AFTER triggers are subject to the following restrictions:

• You cannot specify an AFTER trigger on a view or an object view.

• You cannot write either the :OLD or the :NEW value.

It's not clear why you want to use an AFTER trigger for assigning sequence values to the PK. The common solution is to use a BEFORE trigger.

See example in: How to create id with AUTO_INCREMENT on Oracle?

Answer 2

CREATE SEQUENCE IMOVEL_TRIGGER_SEQ START WITH 1;

Trigger definition:

CREATE OR REPLACE TRIGGER IMOVEL_TRIGGER
BEFORE INSERT ON IMOVEL 
FOR EACH ROW

BEGIN
  SELECT IMOVEL_TRIGGER_SEQ.NEXTVAL
  INTO   :new.GEOCODIGO_IMOVEL 
  FROM   dual;
END;