Before insert trigger to populate null column

Art McEwen

Before insert trigger to populate null column
Hi Listers,

I'm trying to support an application that's migrating from Oracle to DB2. They share the same table structure but in Oracle they use a before insert trigger to populate a mandatory column and in DB2 the existing application explicitly uses the NEXTVAL function. Neither app team wants to change their code so I was proposing a slightly modified trigger to check to see if that column has been populated. But it seems when the trigger is fired it can't cope with a missing value and I get a -407 (Z/os v11 nfm). Doesn't a "before insert" trigger fire before the row is inserted? What am I missing?

I could alter the DB2 table to be not null with default 0 then have the trigger test for 0, but that seems kludgy.

Thanks!

DB2 code
CREATE TABLE HESSDB2.AUDIT_LOG
( SEQUENCE_NUM INTEGER NOT NULL
, SESSION_ID VARCHAR ( 36 ) NOT NULL
, SERVER_ID VARCHAR ( 128 )
, EXTERNAL_OPR_ID VARCHAR ( 256 )
, EXTERNAL_AUDIT_ID VARCHAR ( 128 )
, REQUESTING_SERV_ID VARCHAR ( 512 )
, RQSTING_SERV_DATA_FRMT_TYPCD CHAR ( 3 )
, CREATE_TMESTMP TIMESTAMP NOT NULL
, LOG_SERVICE_TYPCD VARCHAR ( 30 )
, CALLING_SERVICE_ID VARCHAR ( 4000 )
, CLING_SERV_DATA_FRMT_TYPCD CHAR ( 3 )
)

CREATE TRIGGER HESSDB2.MEDTTR28
NO CASCADE BEFORE INSERT
ON HESSDB2.AUDIT_LOG
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
NOT SECURED
-- WHEN (N.SEQUENCE_NUM IS NULL) <=== NOTE COMMENTED OUT to confirm it's the set and not the WHEN
BEGIN ATOMIC
SET N.SEQUENCE_NUM = HESSDB2.AUDIT_LOG_SEQ.NEXTVAL;
END#

INSERT INTO HESSDB2.AUDIT_LOG 00100
(SESSION_ID,CREATE_TMESTMP) 00110
VALUES('12319999',CURRENT TIMESTAMP)# 00120
---------+---------+---------+---------+---------+---------+---------+-------
DSNT408I SQLCODE = -407, ERROR: AN UPDATE, INSERT, OR SET VALUE IS NULL, BUT
THE OBJECT COLUMN SEQUENCE_NUM CANNOT CONTAIN NULL VALUES



Oracle code:

CREATE TABLE AUDIT_LOG (
CREATE_TMESTMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
SESSION_ID CHAR(36) NOT NULL,
SEQUENCE_NUM INT NOT NULL,
LOG_SERVICE_TYPCD VARCHAR(30) NOT NULL,
SERVER_ID VARCHAR(128),
EXTERNAL_OPR_ID VARCHAR(256),
EXTERNAL_AUDIT_ID VARCHAR2(128),
REQUESTING_SERV_ID VARCHAR(64),
RQSTING_SERV_DATA_FRMT_TYPCD CHAR(3),
CALLING_SERVICE_ID VARCHAR(128),
CLING_SERV_DATA_FRMT_TYPCD CHAR(3)
);
create or replace TRIGGER audit_log_biur_trg
before
insert on AUDIT_LOG
for each row
begin
if inserting then
select audit_log_seq.nextval into :new.sequence_num from dual;
end if;
end;



Art McEwen

Sr DBA, Database & Mainframe Support
Health Solutions Delivery Br.
Health Services Cluster
4th flr, 49 Place d'Armes
Kingston ON K7L 5J3

[login to unmask email]<mailto:[login to unmask email]>

Office 613-548-6622
Cell 613-539-3903

Peter Vanroose

Re: Before insert trigger to populate null column
(in response to Art McEwen)

Art,

It seems that, even before the before trigger is fired, Db2 already did some "sanity checking" on the input.
So Db2 (always) disallows NULLs inserted in a NOT NULL column without default value. Irrespective of triggers.

Work-around could be to make that column nullable (maybe with check constraint IS NOT NULL).

This proves that Db2 treats NULLs at a deeper level (hence possibly more efficiently) than Oracle does!

In Reply to Art McEwen:

[...] it seems when the trigger is fired it can't cope with a missing value and I get a -407.
Doesn't a "before insert" trigger fire before the row is inserted? [...]

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Art McEwen

Re: Before insert trigger to populate null column
(in response to Peter Vanroose)

Thanks Peter,

I like my "not null with default 0" solution better because that means the existing code doesn't have to be opened up to add a null indicator.   Just thinking IBM needs to read the dictionary definition of "BEFORE insert"  ;)

 

Art.


In Reply to Peter Vanroose:

Art,

It seems that, even before the before trigger is fired, Db2 already did some "sanity checking" on the input.
So Db2 (always) disallows NULLs inserted in a NOT NULL column without default value. Irrespective of triggers.

Work-around could be to make that column nullable (maybe with check constraint IS NOT NULL).

This proves that Db2 treats NULLs at a deeper level (hence possibly more efficiently) than Oracle does!

In Reply to Art McEwen:

[...] it seems when the trigger is fired it can't cope with a missing value and I get a -407.
Doesn't a "before insert" trigger fire before the row is inserted? [...]

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Peter Vanroose

Re: Before insert trigger to populate null column
(in response to Art McEwen)

If Db2 guarantees no NULLs will be there (e.g. through a check constraint, or through your trigger), the applications won't need no NULL indicators!

In Reply to Art McEwen:

I like my "not null with default 0" solution better because that means the existing code doesn't have to be opened up to add a null indicator.

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Suresh Sane

Before insert trigger to populate null column
(in response to Art McEwen)
Art,


>> Doesn’t a “before insert” trigger fire before the row is inserted?


I was also under that impression a few years ago and promised that I could fix a date column with a BEFORE trigger. The "date" for some randomizing reason was defined as a 9's complement format. I thought I could fix it by reversing that logic but kept receiving an invalid format. For things like nulls, dates etc. Db2 seems to be picky and needs it correct even before the BEFORE trigger is invoked.


Changing the table definition would seem to be the only option for you.


Thx

Suresh


________________________________
From: McEwen, Art (MOHLTC) <[login to unmask email]>
Sent: Wednesday, June 27, 2018 8:41 PM
To: [login to unmask email]
Subject: [DB2-L] - Before insert trigger to populate null column


Hi Listers,



I’m trying to support an application that’s migrating from Oracle to DB2. They share the same table structure but in Oracle they use a before insert trigger to populate a mandatory column and in DB2 the existing application explicitly uses the NEXTVAL function. Neither app team wants to change their code so I was proposing a slightly modified trigger to check to see if that column has been populated. But it seems when the trigger is fired it can’t cope with a missing value and I get a -407 (Z/os v11 nfm). Doesn’t a “before insert” trigger fire before the row is inserted? What am I missing?



I could alter the DB2 table to be not null with default 0 then have the trigger test for 0, but that seems kludgy.



Thanks!



DB2 code

CREATE TABLE HESSDB2.AUDIT_LOG

( SEQUENCE_NUM INTEGER NOT NULL

, SESSION_ID VARCHAR ( 36 ) NOT NULL

, SERVER_ID VARCHAR ( 128 )

, EXTERNAL_OPR_ID VARCHAR ( 256 )

, EXTERNAL_AUDIT_ID VARCHAR ( 128 )

, REQUESTING_SERV_ID VARCHAR ( 512 )

, RQSTING_SERV_DATA_FRMT_TYPCD CHAR ( 3 )

, CREATE_TMESTMP TIMESTAMP NOT NULL

, LOG_SERVICE_TYPCD VARCHAR ( 30 )

, CALLING_SERVICE_ID VARCHAR ( 4000 )

, CLING_SERV_DATA_FRMT_TYPCD CHAR ( 3 )

)



CREATE TRIGGER HESSDB2.MEDTTR28

NO CASCADE BEFORE INSERT

ON HESSDB2.AUDIT_LOG

REFERENCING NEW AS N

FOR EACH ROW MODE DB2SQL

NOT SECURED

-- WHEN (N.SEQUENCE_NUM IS NULL) <=== NOTE COMMENTED OUT to confirm it’s the set and not the WHEN

BEGIN ATOMIC

SET N.SEQUENCE_NUM = HESSDB2.AUDIT_LOG_SEQ.NEXTVAL;

END#



INSERT INTO HESSDB2.AUDIT_LOG 00100

(SESSION_ID,CREATE_TMESTMP) 00110

VALUES('12319999',CURRENT TIMESTAMP)# 00120

---------+---------+---------+---------+---------+---------+---------+-------

DSNT408I SQLCODE = -407, ERROR: AN UPDATE, INSERT, OR SET VALUE IS NULL, BUT

THE OBJECT COLUMN SEQUENCE_NUM CANNOT CONTAIN NULL VALUES







Oracle code:



CREATE TABLE AUDIT_LOG (

CREATE_TMESTMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

SESSION_ID CHAR(36) NOT NULL,

SEQUENCE_NUM INT NOT NULL,

LOG_SERVICE_TYPCD VARCHAR(30) NOT NULL,

SERVER_ID VARCHAR(128),

EXTERNAL_OPR_ID VARCHAR(256),

EXTERNAL_AUDIT_ID VARCHAR2(128),

REQUESTING_SERV_ID VARCHAR(64),

RQSTING_SERV_DATA_FRMT_TYPCD CHAR(3),

CALLING_SERVICE_ID VARCHAR(128),

CLING_SERV_DATA_FRMT_TYPCD CHAR(3)

);

create or replace TRIGGER audit_log_biur_trg

before

insert on AUDIT_LOG

for each row

begin

if inserting then

select audit_log_seq.nextval into :new.sequence_num from dual;

end if;

end;







Art McEwen



Sr DBA, Database & Mainframe Support

Health Solutions Delivery Br.

Health Services Cluster

4th flr, 49 Place d'Armes

Kingston ON K7L 5J3



[login to unmask email]<mailto:[login to unmask email]>



Office 613-548-6622

Cell 613-539-3903



-----End Original Message-----

James Campbell

Before insert trigger to populate null column
(in response to Art McEwen)
My rather old copy of the SQL Standard (2003) has:
"4.38.1 General description of triggers
...
The triggered action is specified to take place either immediately before the triggering event
or immediately after it, according to its specified trigger action time, BEFORE or AFTER."

which tends to imply that the row has to be all ready to insert before the trigger is fired.

Oracle, however has
"This case reveals when Oracle effectively performs its constraint checking: after the statement has been completely executed. "
copied from
https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:952566610034
6646318

which tends to imply that the row only has to be insertable after the trigger(s) has fired.

http://justskins.com/forums/have-trigger-supply-value-115974.html says that SQLServer also
checks constraints before the trigger is fired.

An interesting example of different interpretations of the standard. Or maybe Oracle got
locked into its interpretation before there was a standard.

So it looks like kludge wins.

James Campbell


On 27 Jun 2018 at 20:41, McEwen, Art (MOHLTC) wrote:

>
> Hi Listers,
>  
> I´m trying to support an application that´s migrating from Oracle to DB2.   They share the same
> table structure but in Oracle they use a before insert trigger to populate a mandatory column
> and in DB2 the existing application explicitly uses the NEXTVAL function.   Neither app team wants
> to change their code so I was proposing a slightly modified trigger to check to see if that column
> has been populated.   But it seems when the trigger is fired it can´t cope with a missing value and I
> get a -407 (Z/os v11 nfm).     Doesn´t a "before insert" trigger fire before the row is inserted?   What am
> I missing?  
>  
> I could  alter the DB2 table to be not null with default 0 then have the trigger test for 0, but that
> seems kludgy.
>  
> Thanks!
>  
<snip>
>  
> Art McEwen
>  


---
This email has been checked for viruses by AVG.
https://www.avg.com