Solved

Using IsEmpty

Posted on 2008-10-24
5
944 Views
Last Modified: 2010-05-18
I have the following trigger, CREATE TRIGGER trg_Asgnmnt_OutOfOffice ON dbo.Asgnmnt
FOR UPDATE
AS
      IF UPDATE(OutOfOffice) BEGIN
            UPDATE      dbo.Assignee
               SET      OutOfOffice = I.OutOfOffice,
                  OutOfOfficeUntilTime = I.OutOfOfficeUntilTime,
                  OutOfOfficeUntilDate = I.OutOfOfficeUntilDate
            FROM      dbo.Assignee A,
                  INSERTED I
            WHERE      A.LoginID = I.LoginID
              AND      I.CallID = '00010000'
      END
GO

If OutOfOfficeUntilDate is empty (not NULL I just found it won't be NULL) I need to set it to today's date, what would be the proper syntax i was thinking an if statement after, OutOfOfficeUntilDate = I.OutOfOfficeUntilDate but wasn't sure what the else part of the if statement should be?  Also i only want to change the value of OutOfOfficeUntilDate if that field is empty otherwise I want to keep what is in there.
0
Comment
Question by:wyogirl77
  • 3
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 125 total points
ID: 22800629
Well a datetime field cannot be empty.  It can either be NULL or a valid date.  If you are storing it in a string field (varchar) then you can test it with the len function.

But since you have time and date columns, and SQL doesn't support this, I guess that is what you are doing.  What format do you want your date in?


This will insert a string of each the current date and time into your table.
CREATE TRIGGER trg_Asgnmnt_OutOfOffice ON dbo.Asgnmnt

FOR UPDATE

AS

      IF UPDATE(OutOfOffice) BEGIN

            UPDATE      dbo.Assignee

               SET      OutOfOffice = I.OutOfOffice,

                  OutOfOfficeUntilTime = case when len(I.OutOfOfficeUntilTime)=0 then convert(char(8),getdate(),108) else I.OutOfOfficeUntilTime end,

                  OutOfOfficeUntilDate = case when len(I.OutOfOfficeUntilDate)=0 then convert(char(10),getdate(),101) else I.OutOfOfficeUntilDate end

            FROM      dbo.Assignee A,

                  INSERTED I

            WHERE      A.LoginID = I.LoginID

              AND      I.CallID = '00010000'

      END

GO

Open in new window

0
 

Author Comment

by:wyogirl77
ID: 22800713
The time I don't really care about so much, it is the date, which you are correct I have it in a Varchar(10) field because that is what the program I am working with stores it in, does that change your suggestion at all?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22800770
Only to know what format you want your date in.

There's lots of options and it has to be consistent with what you have in order to be converted back into a date later down the line.

ex.
yyyy-mm-dd
mm-dd-yyyy
mm/dd/yyyy
dd-mm-yyyy
dd/mm/yyyy

0
 

Author Comment

by:wyogirl77
ID: 22813456
The date would need to be in mm/dd/yyyy
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22813504
That is format option 101 which is what I have above.  I still think it would be a good idea to store it as a single datetime field.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 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

18 Experts available now in Live!

Get 1:1 Help Now