Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to check for null value in trigger?

Posted on 2004-08-03
8
Medium Priority
?
2,162 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

660 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