Solved

Auto date column update or insert

Posted on 2007-11-25
15
1,217 Views
Last Modified: 2008-09-08
I have a table in the SQL field . I want a column with Date . I want the column in such a fashion that column reflects the current most date when the row was inserted or  if there is any update on that row later on.
Here are the other columns
RC_REQ_ID(key)
RC_TEST_ID(key)
RQ_REQ_STATUS
CY_CYCLE(Key)
TC_TEST_CASE
RQ_REQ_NAME
RQ_FUNC_AREA
Ex_Date

0
Comment
Question by:kshireesh
  • 5
  • 4
  • 2
  • +2
15 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
to get the current date when inserting the row is "easy": just put a default value "(getdate())" and you have that.
now, to get the value updated upon UPDATEs, you will need a trigger.




CREATE TRIGGER TRG_UPDATE_EX_DATE
ON yourtable
FOR UPDATE
AS
  IF @@NESTLEVEL <= 1
  BEGIN
    UPDATE t
      SET ex_date = getdate() 
    FROM yourtable t
    WHERE EXISTS( SELECT null
         FROM inserted i
         WHERE i.RC_REQ_ID = t.RC_REQ_ID
           AND i.RC_TEST_ID = t.RC_TEST_ID 
          )
  END

Open in new window

0
 
LVL 31

Expert Comment

by:James Murrell
Comment Utility
FYI

Be careful when using getdate() in SQL Server as a Start or End date for a sql statement or Stored Procedure.  This is fairly elementary for most developers, but could give you a problem if you are not aware that the getdate() function in SQL Server returns both the current date and time.

For example if you had a Where clause that went something like this:

    Where Date_Requested >= getdate() - 1


and you wanted records that were requested yesterday....

Since getdate() returns current date and time, depending on the time that the query is run, you probably will not get what you are expecting.  What you probably wanted here was just yesterday's date and a time of 00:00:00, meaning that you wanted all records that were requested yesterday.  Unfortunately, using just a call to getdate(), you won't get all the records.

There are several ways to correct this, and probably can be done easier than I will demonstrate below, but the following line does the trick.

    Where Date_Requested >= cast(convert(varchar(8),getdate()-1,1) as datetime)
 

What I have done is to convert the DateTime value returned from getdate() to varchar  and then cast it back to DateTime.  The effect is to drop the time and reinsert it as 00:00:00.  I am sure someone will tell me an easier way, but let's just don't get caught thinking we are getting all of the records, when we aren't.
0
 
LVL 6

Expert Comment

by:PaultheBroker
Comment Utility
Nice subquery BTW angel.  However, I think that CY_CYCLE is also a key, so of course your trigger would need to join on that too....and I think it was needed for INSERT and UPDATE, so don't you need FOR INSERT, UPDATE - not just FOR UPDATE ??

Another thing - you've specified that NESTLEVEL must be <=1 - so if the table is updated by another trigger or a stored procedure calling this stored procedure, then this won't fire.  I appreciate the finesse, but it might give unanticipated results, don't you think??  What is your thinking?

Jim - Selvia posted this method the other day which is TWICE as efficient as converting the date to a character evidentally:  DATEADD(day, DATEDIFF(day, 0, getdate()), 0) - so spread the word !! :)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>However, I think that CY_CYCLE is also a key
yes, I overlooked that.
now, "ON INSERT" is not necessary IF the default value is used as explained.



CREATE TRIGGER TRG_UPDATE_EX_DATE

ON yourtable

FOR UPDATE

AS

  IF @@NESTLEVEL <= 1

  BEGIN

    UPDATE t

      SET ex_date = getdate() 

    FROM yourtable t

    WHERE EXISTS( SELECT null

         FROM inserted i

         WHERE i.RC_REQ_ID = t.RC_REQ_ID

           AND i.RC_TEST_ID = t.RC_TEST_ID 

           AND i.CY_CYCLE = t.CY_CYCLE

          )

  END

Open in new window

0
 
LVL 6

Expert Comment

by:PaultheBroker
Comment Utility
>now, "ON INSERT" is not necessary IF the default value is used as explained.
yes of course - I missed that .... thnx !

I still don't understand the NESTLEVEL though?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
see:

CREATE TRIGGER TRG_UPDATE_EX_DATE
ON yourtable             <<<<<<<<
FOR UPDATE               <<<<<<<<<
AS
  IF @@NESTLEVEL <= 1
  BEGIN
    UPDATE yourtable     <<<<<<<<<

if you don't check if you are being called by another trigger (ie yourself), you will make this en "endless" loop...
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:kshireesh
Comment Utility
I tried this Trigger. I copied and pasted some rows in the table but  ex_date field remained null.

Is there any time when the trigger executed. I did the copy and paste manually in enterprise manager i don't think it should matter.

0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 43 total points
Comment Utility
>Is there any time when the trigger executed.
upon insert/update

however, in enterprise manager, you have to requery the rows to see the updated values.
0
 

Author Comment

by:kshireesh
Comment Utility
Ok here is what i did ?

I created the Trigger on the table.
then  i copied some rows from somehwere with the same exact colums and pasted some rows in the table where the trigger was created.

Date field remained null. I closed Enterprise manager and reopened it still remained null.

0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 41 total points
Comment Utility
You cannot rely on @@NESTLEVEL, since subprocedure calls could also affect that value.

Instead, check to see if the date column was updated: if so, you know that was done by the trigger, so ignore it [NOTE: be sure not to reference ex_date in your UPDATE statement, or SQL will consider it as UPDATEd; if you might, you need to change this to compare the inserted and deleted ex_date values to be absolutely certain that it changed]:


CREATE TRIGGER tableName_trigger1
ON tableName
AFTER UPDATE
AS
IF @@ROWCOUNT = 0
    RETURN
IF UPDATE(ex_date)
    RETURN

UPDATE tn
SET ex_date = GETDATE()
FROM tableName tn
INNER JOIN inserted i ON tn.RC_REQ_ID = i.RC_REQ_ID
    AND tn.RC_TEST_ID = i.RC_TEST_ID
    AND tn.CY_CYCLE = i.CY_CYCLE
0
 
LVL 6

Expert Comment

by:PaultheBroker
Comment Utility
kshireesh - in Angel's solution, the trigger is only HALF the solution - the other half of the solution which he suggests (and which I missed first time round) is that you need to make sure that the table has a default on this field:

CREATE TABLE blahs
 (...some fields
ex_date datetime = getdate().
... some more fields)

(can also be set on the Design View in Enterprise Manager)

If you don't do this, then change
FOR UPDATE
to: FOR INSERT, UPDATE as I suggested a while ago...

Does this help?
0
 
LVL 6

Expert Comment

by:PaultheBroker
Comment Utility
Angel - the trigger won't trigger itself, so I don't think the situation is quite as dire as you suggest.  You don't need the @@NESTLEVEL statement to prevent an infinite loop based this example. ('infinite' of course being defined by the max nest level - which by default is 32).  I'm not saying its not a good practice (I'm still noodling about that), but its not strictly necessary I think unless you expect the update stored proc to issue multiple update statements on the same table - any comments would be welcome....!!!  Anyway, this is my reference:

 "A trigger, once fired, will ignore any other calls as a result of activity that is part of the same tigger action.    [However] Once you move on to an entirely new statemnt (even within the same overall transaction), the process can start all over again." (Rob Vieira - Pro SQL 2000 Programming, p519).

BTW: looked up that subquery (EXISTS etc) - because it puzzled me why you didn't just use a simple join - turns out that EXISTS is slightly faster, as it stops once its found the first occasion - so thanks for that example.
0
 
LVL 6

Assisted Solution

by:PaultheBroker
PaultheBroker earned 41 total points
Comment Utility
Sorry - use keyword DEFAULT, not '=' in the table definition....
CREATE TABLE blahs

 (...some fields

ex_date datetime DEFAULT getdate().

... some more fields)

Open in new window

0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

772 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

10 Experts available now in Live!

Get 1:1 Help Now