CODEWITHSUNDEEP
sqloracle-databasetriggersinsertnull
user4359659
user4359659

Reputation: 159

Set a value if null inserted in Oracle SQL

I have created a trigger, that will automatically set the first column values as subsequent factorial numbers. However, additionally, I would like to set the second column's value as the value of first incremented by 5, in case a null value is inserted. Here's what I try right now:

create or replace trigger test_tr
before insert on myT
for each row 
begin

IF :new.mNumb is null 
THEN       
    UPDATE myT
    SET mNumb = :new.tab_id + 5;
END IF;


SELECT fac(test_seq.NEXTVAL)
INTO   :new.tab_id
FROM   dual;


end;

But clearly I'm missing something, as nothing happens, the inserted null is still empty.

Upvotes: 2

Views: 16293

Answers (2)

Boneist
Boneist

Reputation: 23578

It all works as expected, using Emmanuel's suggestion to remove the update stmt, as far as I can tell. Here's the test case I used:

drop table test;

create table test (col1 number, col2 number);

create trigger test_trg
before insert on test
for each row 
begin

IF :new.col2 is null 
THEN       
    :new.col2 := :new.col1 + 5;
END IF;

:new.col1 := dbms_random.value;

end;
/

insert into test values (1, 1);

insert into test values (1, null);

insert into test values (null, null);

commit;

select * from test;

which produces the following output:

      COL1       COL2
---------- ----------
.617580128          1
.030570358          6
.555066268

Maybe if you set :new.col1 before dealing with the null col2 scenario, that would work better for you? Doing that produces:

      COL1       COL2
---------- ----------
.302670917          1
.024927489 5.02492749
.667568400 5.66756840

Upvotes: 2

Emmanuel
Emmanuel

Reputation: 14209

Do not re-update the table in your trigger, update the row you're given directly:

...

IF :new.mNumb is null 
THEN       
    :new.mNumb = :new.tab_id + 5;
END IF;

...

Upvotes: 9

Related Questions