CODEWITHSUNDEEP
sqloracle-databaseoracle11g
glw
glw

Reputation: 1664

INSERT ALL fails with trigger setting timestamp

Let's say we have a table like this:

CREATE TABLE test_table 
(
    text   VARCHAR2(200) NOT NULL,
    text2  VARCHAR2(200) NOT NULL,
    ts     TIMESTAMP
);

And we want to insert some data using INSERT ALL:

INSERT ALL 
    INTO test_table ( text, text2 ) VALUES ( 'test', 'test2' ) 
SELECT * FROM dual;

The result is 

1 row inserted.

But, when we want to add trigger, to fill ts column with SYSTIMESTAMP

CREATE OR REPLACE TRIGGER  test_trigger
BEFORE INSERT ON  test_table
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.put_line('text=' || :new.text);
    DBMS_OUTPUT.put_line('text2=' || :new.text2);
    DBMS_OUTPUT.put_line('ts=' || :new.ts);
    :new.ts := SYSTIMESTAMP;
END;
/

Running the same script 

SET SERVEROUT ON;
INSERT ALL 
    INTO test_table ( text, text2 ) VALUES ( 'test', 'test2' ) 
SELECT * FROM dual;

The result is:

text=test
text2=
ts=
INSERT ALL 
INTO test_table ( text, text2 ) VALUES ( 'test', 'test2' ) 
SELECT * FROM dual
Error report -
ORA-01400: cannot insert NULL into ("TEST"."TEST_TABLE"."TEXT2")

Using INSERT works fine 

SET SERVEROUT ON;
INSERT INTO test_table ( text, text2 ) VALUES ( 'test', 'test2' )

The result is 

text=test
text2=test2
ts=
1 row inserted.

Also this works:

INSERT ALL 
INTO test_table ( text, text2, ts) VALUES ( 'test', 'test2', null ) 
SELECT * FROM dual

When I change ts column type to DATE works fine with this kind of trigger. I'm using Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production, I've also checked on Oracle 12c but there is no error, so maybe it's some kind of bug in 11g release?

Upvotes: 2

Views: 163

Answers (1)

Gro
Gro

Reputation: 1683

Nothing seems wrong with your code, it may be a bug with version you are using. That said, what you are trying to achieve is more usually done by following create table statement

CREATE TABLE test_table (
    text   VARCHAR2(200) NOT NULL,
    text2   VARCHAR2(200) NOT NULL,
    ts    TIMESTAMP not null default systimestamp
);

You will not need trigger for this at all.

Upvotes: 3

Related Questions