DB2 Triggers - cant put NOT NULL constraint

ashishsr123 · New User Joined: 06 May 2008 Posts: 33 Location: Chennai

HI,

I need help in DB2 triggers:

Issue:
Table A has many fields out of which 2 fields are getting inserted with NULL values.
I have to stop insertion of NULL values on these 2 fields.

I cant put NOT NULL constraint as it will result in Job abends and online application will throw error.

I am trying using Triggers.

One possible solution is to let table update the fields with NULL values and then let the trigger delete them afterwards ( using After Trigger) ie delete the complete row.

But I am looking at a possibility of using Before trigger.
Let the trigger check if the field have NULL values(to be inserted) and if yes bypass the insert . But is it possible ? This all so that the jobs do not abend .

Kindly advise

enrico-sorichetti · Posted: Thu Apr 30, 2009 12:13 am

the requirement looks like a major database/application change
so the approach should be to fix the programs

going with Your approach You might face issues about data consistency

for example
the end user enters a transaction which updates the table in question
if the transaction is successful he/she might get a message that the insert
was done and maybe produce an hardcopy of the event

but then a triggered event clears up the update

dick scherrer · Posted: Thu Apr 30, 2009 1:12 am

Hello,

Craq Giegerich · Posted: Thu Apr 30, 2009 1:23 am

dbzTHEdinosauer · Posted: Thu Apr 30, 2009 1:27 am

I recently came off a site where they had the same problem.
they figured that there are not that many programs
that are doing a row insert -
so fix all those programs. Still had NULLs being INSERTed.

so I put a trigger, with a SIGNAL and an error message.
Found them,
required some quick compiles/binds/links/promotion to production,
but that was the lesser of two evils

an hour or so down time
crap in the database that made balancing the books impossible

the jerkoff (manager) that allowed the garbage code to go to production,
had to stand-up, admit, and then require the trigger to be implemented.

actually, the only hard part was making the manager fess-up.
that's the problem that you have -
forcing management to admit to the problem
fix the problem
and probably endure some embarrassment.

ashishsr123 · New User Joined: 06 May 2008 Posts: 33 Location: Chennai

dick scherrer · Posted: Thu Apr 30, 2009 1:47 am

Hello,