Trigger for updating date ?

Question

Trigger for updating date ?

dforsman

Newbie

Points: 4
More actions
May 8, 2009 at 7:34 am

#129870

I have a web page for updating personal information that is connected to the table "tblPerson". Anytime any data in the record is updated I would like the field called "DateUpdated" to be populated with the current date.
Thanks for the help.
Dennis
USE [expertDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TimeUpdate]
ON [dbo].[tblPerson]
FOR UPDATE
AS
Update tblPerson set
DateUpdated = GetDate()

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

your current trigger would update everything, where you really want to restrict it to the rows that were updated.

inside a trigger, there are two virtual tables called INSERTED and DELETLED, which contain the new and old values for anything being updated.

these virtual tables have the same columns as the table the trigger is tied to.

you want to join your table against that INSERTED table and use the UPDATE... FROM... syntax

ALTER TRIGGER [dbo].[TimeUpdate]

ON [dbo].[tblPerson]

FOR UPDATE

AS

Update tblPerson

SET DateUpdated = GetDate()

FROM INSERTED

WHERE PersonID = INSERTED.PersonID --assuming this is the PK column for the table.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dforsman Newbie Points: 4 More actions · Answer 2

Sorry I am new at the sql statements. What do I do with the code you have provided?

Do I just run this against my database and delete the trigger that I initially created?

Lowell SSC Guru Points: 323518 More actions · Answer 3

you pasted your current trigger...i modified it.

however, i made a WAG(Wild *** Guess) as to whether your table tblPerson had a PersonId in it.

you'd have to modify it to be correct, run it in SSMS on your test server that had the same table/structure.

then test it by updating a person, confirm the column got updated, and finally promote it to your production database as well.

If your a bit unsure, is there someone at your organization you can cross check this with?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dforsman Newbie Points: 4 More actions · Answer 4

Got the following message.

Msg 209, Level 16, State 1, Procedure dbo.timeupdate, Line 8

Ambiguous column name 'personid'.

Lowell SSC Guru Points: 323518 More actions · Answer 5

my WAG on the column name was perfect!

change that last line to this:

WHERE tblPerson.PersonID = INSERTED.PersonID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dforsman Newbie Points: 4 More actions · Answer 6

Msg 208, Level 16, State 6, Procedure dbo.timeupdate, Line 5

Invalid object name 'dbo.timeupdate'.

noeld SSC Guru Points: 96590 More actions · Answer 7

Please, post the DDL of your table so that Lowell don't have to WAG anymore 🙂

* Noel

Lowell SSC Guru Points: 323518 More actions · Answer 8

if the trigger does not exist, you can't ALTER it..change to CREATE TRIGGER.....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 9

Steve Jones - SSC Editor

SSC Guru

Points: 736642

May 8, 2009 at 8:49 am

#991164

You can't name the table and procedure the same thing.