Writing to a history table - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Writing to a history table

Author

Topic

AlanPBates
Starting Member

34 Posts

Posted - 2010-06-24 : 14:42:09

I have a table -

CREATE TABLE [dbo].[SortStartEnd](
[RowIdentity] [varchar](20) NULL,
[SOS] [bit] NULL,
[EOS] [bit] NULL,
[SortTime] [datetime] NULL
) ON [PRIMARY]

And a History Table -

CREATE TABLE [dbo].[SortStartEnd_History](
[RowIdentity] [varchar](20) NULL,
[SOS] [bit] NULL,
[EOS] [bit] NULL,
[SortTime] [datetime] NULL,
[SortID] [int] NULL
) ON [PRIMARY]

The SortStartEnd table will only contain 2 rows at any given time -

ROWIdentity SOS EOS SORTime
------------ ----- ----- ------------------------
StartOfSort False False 2010-06-23 16:12:14.060
EndOfSort False True 2010-06-23 16:13:38.000

My trigger on the SortStartEnd table duplicates data from the SortStartEnd to SortStartEnd_History table.

ALTER TRIGGER [dbo].[trg_EOS]
ON [dbo].[SortStartEnd]
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
IF EXISTS (SELECT EOS FROM [SortStartEnd]
WHERE (EOS = '1'))

INSERT INTO [dbo].[SortStartEnd_History]
([RowIdentity]
,[SOS]
,[EOS]
,[SortTime]
,[SortID] )
SELECT [RowIdentity]
,[SOS]
,[EOS]
,[SortTime]
FROM [SortStartEnd]
END

Now I want the SortID for both the rows to be the same -

eg :

ROWIdentity SOS EOS SORTime SORTID
------------ ----- ----- ------------------------ --------
StartOfSort False False 2010-06-23 12:12:14 1
EndOfSort False True 2010-06-23 16:23:38 1
StartOfSort False False 2010-06-23 17:15:19 2
EndOfSort False True 2010-06-23 19:13:38 2
StartOfSort False False 2010-06-23 20:12:35 3
EndOfSort False True 2010-06-23 23:29:19 3
StartOfSort False False 2010-06-24 05:10:59 4
EndOfSort False True 2010-06-24 10:13:38 4

and so on .....

How do I get the SortID to represent a pair as above ?

Thanks,

Alan

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-24 : 14:51:31

I would store the data in one record with SOSTime and EOSTime.

No, you're never too old to Yak'n'Roll if you're too young to die.

AlanPBates
Starting Member

34 Posts

Posted - 2010-06-24 : 14:59:49

How would I do that .. I'm sorry I don't follow you ..

I cannot modify the first table. Its existing and another application access it for other purposes.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-24 : 15:18:32

CREATE TABLE [dbo].[SortStartEnd_History](
[SOS_SortTime] [datetime] NULL,
[EOS_SortTime] [datetime] NULL
) ON [PRIMARY]

ALTER TRIGGER [dbo].[trg_EOS]
ON [dbo].[SortStartEnd]
AFTER INSERT,UPDATE
AS
BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT EOS FROM [SortStartEnd]
WHERE (EOS = '1'))

INSERT INTO [dbo].[SortStartEnd_History]
SELECT
(select SortTime from SortStartEnd where RowIdentity='StartOfSort'),
(select SortTime from SortStartEnd where RowIdentity='EndOfSort')
END

No, you're never too old to Yak'n'Roll if you're too young to die.

AlanPBates
Starting Member

34 Posts

Posted - 2010-06-24 : 15:32:14

Webfred ..

Thanks .. it worked ..

Alan.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-24 : 16:57:34

welcome