Solved

How to check for null value in trigger?

Posted on 2004-08-03
8
2,129 Views
Last Modified: 2012-08-13
I have a Table (Tbl_1) into which a record is inserted from time to time. Once a record is inserted into Tbl_1 then I will have to use the columns from the new record and populate other tables. The column values in Tbl_1 can be "NULL". I am planning to write a DB Trigger to take care of creating records in other tables. I am not sure how I can handle existence of "NULL" column values. If a column value is NULL then I will will have to insert "NULL" value.  What is the conditional statement I can make use of to check for "NULL" value? Any help is very much appreciated.

Kind regards
0
Comment
Question by:samble
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11704960
>>What is the conditional statement I can make use of to check for "NULL" value?<<

Where ColumnName Is Null

Or
Where ColumnName Is Not Null
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11705021
To be more precise,

  WHERE inserted.ColumnName IS NULL

0
 

Author Comment

by:samble
ID: 11705056
Thanks for the quick response. The issue is I have to check on multiple columns and my SQL statement will be subject to the condition. Just for example if I have four columns then depending on the value of each column the SQL statement will have to be different. Potentially I can have upto 16 different SQL statements. Do you agree with this?

Kind regards
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11705104
>>Do you agree with this?<<
Without seeing your SQL statement it is a littel difficult to hazard a guess.  But in general I would say no, it is not necessary.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11705123
Also, when you get a chance please followup on your abandoned questions.  Here are all your open questions:

1 06/24/2004 200 How to prevent the installer from overwr...  Open Visual Basic
2 06/28/2004 125 Starnge problem integrating Crystal repo...  Open Crystal Reports
3 06/30/2004 250 Why doesn't the subreport show up in the...  Open Crystal Reports
4 07/06/2004 250 How to conditionally choose a sub-report...  Open Crystal Reports
5 07/06/2004 125 Does teh DSN name have to be hard coded ...  Open Crystal Reports
6 07/07/2004 500 How can I find out the number of pages i...  Open Crystal Reports
7 07/08/2004 125 Is there a way to suppress everything bu...  Open Crystal Reports
8 07/12/2004 125 Is there a way to add "Print setup" butt...  Open Crystal Reports
9 08/03/2004 125 How to check for null value in trigger?  Open Microsoft SQL Server

Thanks.
0
 
LVL 5

Accepted Solution

by:
hkamal earned 125 total points
ID: 11705848
samble, a trigger is effectively a proc attached to a table, you can easily create a conditional dynamic sql
viz:

CREATE TABLE Customers (CustomerName VARCHAR(100) NULL, CustomerAge INT NULL, ....)
GO
                 
CREATE TRIGGER cust_insert
ON Customers
FOR INSERT AS
DECLARE @sql=varchar(255)
SELECT @sql="insert TableA (col1) select "+CASE WHEN CustomerName = NULL THEN "NULL" ELSE CustomerName  END
SELECT @sql=@sql+"insert TableB (col1) select "+CASE WHEN CustomerAge = NULL THEN "NULL" ELSE CustomerAge  END
..
..
EXEC (@sql)
GO

INSERT Customers SELECT "George W Bush", 99
SELECT * FROM Customers


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11707562
I afraid this:
CASE WHEN CustomerName = NULL

Will never be true.

You cannot compare to a Null using an equal sign.

To see what I mean, try this from Query Analyzer:
If Null = Null
   Select 'Null = Null'
Else
   Select 'Null <> Null'
0
 

Author Comment

by:samble
ID: 11708077
You are right about your observation. I will have to use "IS NULL" operator to compare. Despite this flaw the logic is generally okay unless of course I am totally wrong here.

Kind regards
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question