469,613 Members | 1,888 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,613 developers. It's quick & easy.

How to prevent duplicate successive inserts in a certain timeframe

24
Hi Everyone,

I am a bit of a hobby programmer (read newbie), and I have been searching for a solution to a SQL problem for a recent pet project. I discovered that there are a number of brilliant minds hanging around here, and I was hoping someone could point me in the right direction.

I'm using MS SQL 2005 and I have created a table of "level changes" that basically stores the device name [DeviceID], the previous level [PreviousValue] and the new level [NewValue] along with a timestamp as follows:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE [dbo].[InsteonLevelChange](
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,
  3.     [DeviceID] [nvarchar](50) NOT NULL,
  4.     [Address] [nvarchar](8) NULL,
  5.     [PreviousValue] [int] NULL,
  6.     [NewValue] [int] NOT NULL,
  7.     [UpdateTime] [datetime] NOT NULL CONSTRAINT [DF_InsteonLevelChange_UpdateTime]  DEFAULT (getdate()),
  8.  CONSTRAINT [PK_InsteonLevelChange] PRIMARY KEY CLUSTERED 
  9. (
  10.     [ID] ASC
  11. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
  12.  CONSTRAINT [IX_InsteonLevelChange] UNIQUE NONCLUSTERED 
  13. (
  14.     [DeviceID] ASC,
  15.     [UpdateTime] ASC
  16. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  17. ) ON [PRIMARY]
I have a vbscript that inserts the deviceid, old and new values every time a device change is detected. The time is automatically inserted by SQL to avoid any time formatting issues if the data comes from sources with different regional formats. The problem I'm having is that the script occasionally fires twice in a row (I can't change this part) for a single level change so I end up with something like the following in my table:

Expand|Select|Wrap|Line Numbers
  1. DeviceID, Prev, New, Time
  2. D1, 0, 100, 2:00:00 PM
  3. D1, 100, 50, 3:00:00 PM
  4. D1, 100, 50, 3:00:00 PM
This is not what I want. I tried adding a unique constraint on the deviceid, previous, new and time columns, but this didn't help since the time (although displayed only to the second) is stored with a resolution of 1 tick and the updates are several hundred ticks apart. In my example the time values for rows 2 and 3 would be stored as 3:00:00.123 and 3:00:00.456 so the constraint was not actually violated.

I tried implementing David Portas' trigger suggestion from this post http://www.thescripts.com/forum/thread144850.html but this prevented me from inserting _any_ new rows into the table, triggering the RAISERROR block each time. My trigger code is as follows:

Expand|Select|Wrap|Line Numbers
  1. CREATE TRIGGER [dbo].[trg_InsertInsteonLevelChange] 
  2.    ON  [dbo].[InsteonLevelChange] 
  3.    AFTER INSERT
  4. AS 
  5. BEGIN
  6.     IF EXISTS
  7.      (SELECT T1.DeviceID
  8.       FROM inserted AS T1 JOIN dbo.InsteonLevelChange AS T2
  9.         ON T1.DeviceID = T2.DeviceID
  10.         AND T1.PreviousValue = T2.PreviousValue
  11.         AND T1.NewValue = T2.NewValue
  12.         AND T1.UpdateTime < DATEADD(second,10,T2.UpdateTime))
  13.     BEGIN
  14.       ROLLBACK TRAN
  15.       RAISERROR('Level change repeated within 10 seconds',16,1)
  16.     END
  17.  
  18. END
Does anyone have any ideas of why this trigger is not working, or perhaps a better way to prevent duplicate updates (device, lastlevel, newlevel) from entering my table within a certain time period? Please note that duplicate updates should be allowed after a certain time period since it is also possible for an update to be missed completely (i.e. there could be 2 successive "D1, 100, 50" updates if a "D1, 50, 100" was missed in between) but they should be at least several seconds apart.

I would like to keep the logic in the database if possible, and try to avoid using stored procedures unless absolutely necessary, however, if that is the best/only way to do it then I am all ears. I look forward to any and all suggestions. Thanks for reading this far!

Cheers,

JE
Jan 12 '08 #1
0 1810

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by entoone | last post: by
6 posts views Thread by nick4soup | last post: by
9 posts views Thread by Catherine Jo Morgan | last post: by
6 posts views Thread by Arthur Dent | last post: by
4 posts views Thread by =?Utf-8?B?QXNhZg==?= | last post: by
6 posts views Thread by teser3 | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.