Re: Use a trigger to remove NULL values?

CJ wrote:

I am having some issues with an application that enters NULL values to
an oracle 9.2 db. The application inserts multiple rows with NULL,
which are currently removed by using standard SQL (delete) on two
tables (table a contains the value, table b contains the relation).

I therefore wonder if this would be an appropriate place to use a
trigger?
If yes, any examples or suggestion on how to do this?
If no, what would be the appropriate way of automatic cleaning up this
problem? (I can not rewrite the application)

I'm not sure I understand the problem. Do you want the db to simply ignore those insert statements that contain nulls? Is every column null, or just some?

If you want the db to simply ignore insert statements containing nulls, a trigger would work. That's kind of odd behavior, though. If I issue an insert statement, I expect it to either insert a record or return an error. Failing silently is unusual behavior, but you can certainly implement it with a trigger.

Better to simply make a not null constraint, but if this breaks your app, the trigger might make sense. You can also set a default value for the column which would get rid of the nulls, but I'm not sure if this would be what you want.

//Walt
.

Relevant Pages

  • Re: Prevent Duplicates in a field
    ... since the last time that I wrote a trigger but you can ask in the ... >DisplayAssetNumber Set to No duplicates but each allows nulls. ... >happend once there is a null entry in either of the no diplicate fields. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Row Level Trigger
    ... You can use the inserted table to check for NULLs and update them ... We have a trigger in Oracle that will change the NULL to ... > We are converting to SQL Server and the code takes much longer to run ...
    (microsoft.public.sqlserver.programming)
  • Re: How to prevent writing a record when unwanted data is detected?
    ... Your trigger will fail if more than one row is inserted. ... Just declare the column NOT NULL if you want to prevent NULLs being ... It looks like you want to accept the valid rows in the UPDATE/INSERT but not ... statements so that they don't insert invalid data. ...
    (microsoft.public.sqlserver.programming)
  • Disabling triggers
    ... Does anyone know if there is/are a T-SQL commandto ... (a bit like SET ANSI ... NULLS OFF/ON)? ... All I can think of doing is editing the existing trigger ...
    (microsoft.public.sqlserver.programming)
  • Re: Use a trigger to remove NULL values?
    ... which are currently removed by using standard SQL on two ... or this is out of your hands, then yes, I think a trigger would be ... Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL ... Database development questions? ...
    (comp.databases.oracle.server)
Loading