Solved

How to check for null value in trigger?

Posted on 2004-08-03
8
2,080 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
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

685 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