Solved

How to check for null value in trigger?

Posted on 2004-08-03
8
2,061 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

777 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