Solved

How to check for null value in trigger?

Posted on 2004-08-03
8
2,042 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
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Deadlocks 12 50
SQL Server - Slabs 9 38
Microsoft Azure SQL - create a read only user 2 14
MSSQL: Substring and Charindex error 7 19
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now