Trigger to set getdate() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger to set getdate()

Author

Topic

emoffster
Starting Member

4 Posts

Posted - 2010-10-06 : 05:53:55

Hi,
Please help a newbie with a trigger that works. :-)
I've set up a supportticket table, at work, which looks like this (employees fills in "perssign2", "system" and "error" on a ASP-page):

ID - int (primary key, counter)
regdttime - datetime (default getdate())
perssign2 - varchar(6)
system - varchar(50)
err - varchar(MAX)
startdttime - datetime
sign varchar(2)
finished char(1)
findttime datetime
solution varchar(MAX)

What I want is, when any of us fill in "sign" (starts job, ex "EJ"), a trigger sets getdate() in the column "startdttime", on that row. "startdttime" is not supposed to change at other updates on that row.

When supportticket is finished we check column "finished" (value "Y") and then "findttime is supposed to get a timestamp, getdate().

TIA!
Emil

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-06 : 06:04:49

create trigger dbo.trgSIGN
on dbo.supporttickettable
after update
as

if update(sign)
update x
set x.startdttime = getdate()
from dbo.supporttickettable as x
inner join inserted as i on i.id = x.id
go

N 56°04'39.26"
E 12°55'05.63"

emoffster
Starting Member

4 Posts

Posted - 2010-10-06 : 08:18:29

Thanks! Unfortunately it seems startdttime gets updated with that timestamp every time anything is updated on that row, unfortunately... Probably something on the ASP-page that updates sign everytime, and therefore startdttime also is being updated.
Is it possible to set a IF statement on the trigger, which checks if say startdttime already has got a value, and then does nothing?
Suggestions?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-10-06 : 08:31:54

If it is nullable field with no default value, you can add WHERE clause to prevent further updates like below:


update x
set x.startdttime = getdate()
from dbo.supporttickettable as x
inner join inserted as i on i.id = x.id
where x.startdttime is null

Harsh Athalye
http://www.letsgeek.net/

emoffster
Starting Member

4 Posts

Posted - 2010-10-06 : 09:37:14

It works, thank you very much!
However, on the ASP "edit support ticket page" I get the following error, when saving: "Row cannot be located for updating. Some values may have been changed since it was last read.".
Although it's there, in the DB. Anyone got a clue?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-06 : 10:53:52

[code]CREATE TRIGGER dbo.trgSIGN
ON dbo.SupportTicket
AFTER UPDATE
AS

SET NOCOUNT ON

IF UPDATE([SIGN])
UPDATE x
SET x.StartDtTime = GETDATE()
FROM dbo.SupportTicketTable AS x
INNER JOIN inserted AS i ON i.ID = x.ID
WHERE x.StartDtTime IS NULL
GO[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-06 : 10:54:29

For the error message, I would track down the code that throws the error message.

N 56°04'39.26"
E 12°55'05.63"

emoffster
Starting Member

4 Posts

Posted - 2010-10-06 : 11:30:03

Thank you, guys! Seems like you've helped me accomplish what I wanted!