Solved

Auto date column update or insert

Posted on 2007-11-25
15
1,220 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]
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
 
LVL 142

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 142

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 43 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 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 41 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 41 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 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

20 Experts available now in Live!

Get 1:1 Help Now