Fixing records after insert into table

Question

Post reply

Fixing records after insert into table

Glenn

SSC Veteran

Points: 289
More actions
March 20, 2008 at 10:30 am

#120781

I have a process that writes records to a table. There is a date field in this record and occasionally the year gets written incorrectly (one year early). For many reasons I can't touch the process inserting the records.
Let's say a record gets written today with a date of 3/20/2007.
On a table that has little restraints is is safe to assume that new records are always put at the end of the table.
Where I'm going is that I could just keep a counter in a table and then any records with a count greater than that, just make the year equal to the system year.
Would that work?
Or, not knowing triggers very well, is there something I could do with a trigger?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Mike Menser Hall of Fame Points: 3679 More actions · Answer 1

Is the date always supposed to be the same as the system date on the date it is inserted?

Glenn SSC Veteran Points: 289 More actions · Answer 2

Yes, the system date would work, but I'm not sure how to get it in the field?

Glenn SSC Veteran Points: 289 More actions · Answer 3

to be a little clearer it's only the year that I have to fix in the following format

3/8/2007

thanks

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 4

Exploring the trigger route...

CREATE TABLE [dbo].[TestTable1](

[n] [int] IDENTITY(1,1) NOT NULL,

[BadDate] [datetime] NOT NULL,

CONSTRAINT [PK_TestTable1] PRIMARY KEY CLUSTERED

(

[n] ASC

)

go

create TRIGGER [dbo].[trg_Test1]

ON [dbo].[TestTable1]

for INSERT

AS

BEGIN

SET NOCOUNT ON;

Declare @Year int

Select @Year = Year(getdate())

update

TestTable1

set BadDate = dateadd(yy,1,inserted.BadDate)

From inserted

INNER JOIN TestTable1 ON inserted.n = TestTable1.n and Year(inserted.BadDate) <> @Year

END

go

insert into TestTable1(BadDate) values ('2/3/2007')

insert into TestTable1(BadDate) values ('2/12/2008')

go

Select * From TestTable1

The inserted table is a hidden table that has the same structure as the table being updated or inserted to in the sql statement, and we can join from the inserted table to the real table on the primary key to limit our set to those just inserted and only update those records whose year value is different from the current year according to server time.

Anyone see any issues with this?

Glenn SSC Veteran Points: 289 More actions · Answer 5

Thanks, I think that is getting me close. The one question I had was does this do the date add all the time? I only need to do it if the date to be inserted is one less that the system year?

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 6

... only update those records whose year value is different from the current year according to server time.

Right now, it updates those that are different, and it only adds one. If your situation is different, then the code needs to be modified accordingl...