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

x
?
Solved

Auto date column update or insert

Posted on 2007-11-25
15
Medium Priority
?
1,227 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
[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
  • 5
  • 4
  • 2
  • +2
15 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20346456
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
ID: 20346857
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
ID: 20347331
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20348329
>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
ID: 20349187
>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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20349337
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
 

Author Comment

by:kshireesh
ID: 20350129
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 172 total points
ID: 20350638
>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
ID: 20350944
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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 164 total points
ID: 20353367
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
ID: 20378219
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
ID: 20379573
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 164 total points
ID: 20380020
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.

705 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