0

I got an issue with INSERT ALL statement for the below table and this table has a Before Insert trigger which automatically populates the date when the ID_NO column is populated.

CREATE TABLE TestA 
(
    ID_NO varchar2(30) PRIMARY KEY,
    BDATE TIMESTAMP
)

CREATE TRIGGER BDATE_TR
BEFORE INSERT OR UPDATE ON TestA
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
    IF INSERTING OR UPDATING THEN
        SELECT SYSTIMESTAMP INTO :NEW.BDATE FROM DUAL;
    END IF;
END;

Now when inserting the following statement is successful.

INSERT INTO TestA (ID_NO) VALUES ('A1');

But using INSERT ALL statement like this:

INSERT ALL
INTO TESTA (ID_NO) VALUES ('A2')
INTO TESTA (ID_NO) VALUES ('A3')

SELECT * FROM dual;

I am getting an error

ORA-01400: cannot insert NULL into TestA.ID_NO.

Please let me know what mistake I made when using the INSERT ALL statement?

Improve this question
0

2 Answers 2

Reset to default
0

Your main issue is not about the syntax of your trigger, but the mistake to use one at all.

Your version of Oracle is very obsolete, the end of support of Oracle 12c was July 31, 2022, almost 3 years ago.

The quite simple solution is upgrading to Oracle 23. Then remove the trigger and use the option default on null for insert and update systimestamp for this column. Then decide if you want to skip the column in your update commands or set it to systimestamp there. In your insert commands, you don't need to provide this column.

See this sample fiddle that shows the behaviour in Oracle 23c for some different insert and update commands.

Even if you can't upgrade at the moment, you should not use a trigger whose single purpose is to set a column to the current timestamp. In this case, set the default value of the column to systimestamp and if you execute update commands, then simply set the timestamp in those commands rather than using a trigger. Then upgrade asap and set the new option.

By the way, your trigger (if it would work) would prevent that the column could ever get a different value than the current timestamp. If this is really intended and you want to prevent people from saving different timestamps there, you should use a check constraint to make this sure. And you should very likely make this column not nullable because you obviously never want it to be null.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

The purpose of the trigger is obviously to set bdate with every write access, no matter what the insert or update looks like. With default on null for insert and update systimestamp one would have to explicitly update the bdate with null. But if we must remember to do this in every update, we could write systimestamp right away, and we could still write a fake timestamp. In my opinion, a trigger is the only correct solution for such a task in Oracle so far.
Well, in my opinion it's a very bad and unclean idea to use a trigger whose only purpose is to set the value of a column which can simply be set by the update command. Triggers should only be used for such tasks which can't simply be implemented in the insert or update command, but have more complex logic. But of course, that's simply my opinion, we don't need to agree in this point ;)
@ThorstenKettner To the point we could still write a fake timestamp: If you want to avoid that, I think a trigger is the wrong way. Use a check constraint and also the application which fires the insert or update commands to rule out undesired values in a column, as I mentioned in my answer.
0

This is obviously a bug in INSERT ALL in Oracle 11g and 12c. It seems it got fixed in Oracle 21c.

The trigger can be shortened to:

CREATE OR REPLACE TRIGGER bdate_tr
BEFORE INSERT OR UPDATE ON testa
FOR EACH ROW
BEGIN
  :new.bdate := systimestamp;
END bdate_tr;
/

And the bug would be something along the lines of: INSERT ALL damages the row data to be inserted, when the table contains a timestamp and you set that column in the trigger.

Tests:

The obvious solution is to upgrade from your outdated Oracle version to a more current one. As long as you have to work with an old buggy version, I suggest just to not use INSERT ALL at all. The statement can be re-written to:

INSERT INTO testa (id_no)
  SELECT 'A2' FROM dual UNION ALL SELECT 'A3' FROM dual;

Demo: https://dbfiddle.uk/WZuJbsZT

(You cannot just INSERT INTO testa (id_no) VALUES ('A2'), ('A3'); unfortunately, as Oracle did not support this standard SQL syntax before Oracle 21c.)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.