Inclusion of First Record using a select SUM

Question

In certain routine I have a Trigger that runs a code similar to the one below.

I’m having trouble in the first record, as in the first record does not have any code, the sum of + 1 is not being held, and I can’t do the Insert.

Have some way to play a default value, or some Oracle feature that bypasses when entering the first record?

INSERT INTO TABELA1                                                                  (
 CODIGO
,CAMPO2
,CAMPO3)

VALUES

(
(select max(CODIGO)+1 from TABELA1)
,VALOR2,
VALOR3)
);

Answer 1

For cases like this, you can use the function NVL: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm

---

Using it, you generate a default value if the MAX return null, soon your Insert would look like this:

INSERT INTO TABELA1 ( CODIGO ,CAMPO2 ,CAMPO3)

VALUES

( (select nvl(max(CODIGO),0) +1 from TABELA1) ,VALOR2, VALOR3) );

That way, when the max return null, it will be replaced by zero and its sum will return 1.

---

See this online example.