Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Join Date
    Jan 2009
    Posts
    19

    Unanswered: DB Constraint based upon previous value

    I have a table like this:

    TagKey TagID TagvALUE TagTimeStamp

    I want to create this constraint:

    "If the previous TagValue = current TagValue then do not update".

    Can anyone help me, please?. Thanks.

  2. #2
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    hi,
    you can write a trigger (insert or update) for this problem.
    Within the trigger, write a code to get the tagvalue of the maximun TagTimeStamp and store it in a variable. compare that variable value with the inserted magic table value. if both are same then dont update else update.

    The logic here is that previous inserted values will have the latest TagTimeStamp, you can use that for your purpose.

  3. #3
    Join Date
    Jan 2009
    Posts
    19
    Thanks Parangiri. I am excited that you understood my problem very well. As i am beginner, can you kindly provide sample code to write this trigger?.

    You are right. Last TagTimeStamp should give value for that code.

    BTW, what is magic table?.

  4. #4
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    For example let us have a insert trigger written for the below table,
    table name : employee

    insert into employee values('E001','Ram','25'), insert trigger will be triggered

    the values inserted will be stored in a table called inserted withinin the trigger.

    table called inserted which is created in the trigger is known as magic tables.

    similarly for delete statement, deleted magic table will be created if delete trigger is written for the table.

    For update statement, both inserted and deleted tables will be automatically created.

    i hope u got it


    CREATE TRIGGER trig_tag
    ON tag
    FOR UPDATE

    AS
    declare @tagval varchar(100)
    select @tagval = TagValue from TagTimestamp = (select max(timestamp) from tag)

    if exists (select 1 from inserted where inserted.Tagvalue = @tagval)
    rollback
    else
    commit

    Above is the solution trigger for your problem. minor changes might be needed for the above code. hope it helps u. :-)
    Last edited by parangiri; 01-17-09 at 10:57.

  5. #5
    Join Date
    Jan 2009
    Posts
    19
    This part is giving an error:

    select @tagval = TagValue from TagTimestamp = (select max(timestamp) from tag)

    But almost there. I will try.

  6. #6
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    change it like the below. As i told u, there might be small bugs in that trigger written

    select @tagval = TagValue from tag where TagTimestamp = (select max(Tagtimestamp) from tag)

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    EDIT: I think I've got the wrong end of the stick, ignore this post for now.
    Code:
    CREATE TABLE dbo.your_table (
       TagID        int
     , TagvALUE     int
     , TagTimeStamp datetime
    )
    GO
    
    CREATE TRIGGER dbo.test_trigger
      ON dbo.your_table
      INSTEAD OF INSERT
    AS
      BEGIN
    
        INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
        SELECT i.tagid
             , i.tagvalue
             , i.tagtimestamp
        FROM   inserted As [i]
         LEFT
          JOIN (
                SELECT your_table.tagid
                     , your_table.tagtimestamp
                     , your_table.tagvalue
                FROM   dbo.your_table
                 INNER
                  JOIN (
                        SELECT tagid
                             , Max(tagtimestamp) As [max_timestamp]
                        FROM   dbo.your_table
                        GROUP
                            BY tagid
                       ) As [latest_records]
                    ON your_table.tagid = latest_records.tagid
                   AND your_table.tagtimestamp = latest_records.max_timestamp
               ) As [y]
            ON y.tagid = i.tagid
           AND y.tagvalue = i.tagvalue
        WHERE  y.tagid IS NULL
    
      END
    GO
    
    --Valid records
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
      VALUES (1, 1, '20090101')
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
      VALUES (2, 1, '20090101')
    
    --Dupes
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
      VALUES (2, 9, '20090101') --New value!
    
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
      VALUES (2, 1, '20090101') --completely identical record
    
    --Valid record
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
      VALUES (2, 1, '20100101')
    
    SELECT *
    FROM   dbo.your_table
    
    GO
    DROP TABLE dbo.your_table
    Last edited by gvee; 01-19-09 at 08:29.
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Example above correctified.
    EDIT: darn - still not quite right
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Actually, it does work* - my sample test data was flawed!

    There's one problem I see though, there's nothing in the above implementation that stops you from inserting a record [B]earlier[B] than the latest date - which could invalidate the constraint.

    I can't think of any solution that would be infallable...
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2007
    Posts
    183
    Code:
    CREATE TABLE dbo.your_table (
       TagID        int
     , TagValue     int
     , TagTimeStamp datetime
    )
    GO
    CREATE TRIGGER dbo.test_trigger
      ON dbo.your_table
      AFTER INSERT
    AS
    
    IF EXISTS (
    		SELECT		TagID,
    				TagValue
    		FROM		(
    					SELECT		yt.TagID,
    							yt.TagValue,
    							DENSE_RANK() OVER (PARTITION BY yt.TagID ORDER BY yt.TagTimeStamp DESC, NULLIF(yt.TagValue, i.TagValue), i.TagValue DESC) AS recID
    					FROM		dbo.your_table AS yt
    					INNER JOIN	inserted AS i ON i.TagID = yt.TagID
    					WHERE		yt.TagTimeStamp <= i.TagTimeStamp
    				) AS d
    		WHERE		recID <= 2
    		GROUP BY	TagID,
    				TagValue
    		HAVING		COUNT(*) > 1
    	) 
    	BEGIN
    		RAISERROR('Previous record already have same TagValue.', 16, 1)
    		ROLLBACK TRAN
    	END
    GO
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
      VALUES (1, 1, '20090101')
    GO
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
      VALUES (2, 1, '20090101')
    GO
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
      VALUES (2, 9, '20090101')
    GO
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
      VALUES (2, 1, '20090101')
    GO
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
      VALUES (2, 1, '20100101')
    GO
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
      VALUES (2, 1, '20090701')
    GO
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
      VALUES (2, 2, '20090801')
    GO
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
    SELECT 4, 4, '20090801'
    GO
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
    SELECT 4, 4, '20090901'
    GO
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
    SELECT 3, 1, '20090901' UNION ALL
    SELECT 3, 1, '20090901'
    GO
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
    SELECT 5, 5, '20090901' UNION ALL
    SELECT 6, 6, '20090901'
    GO
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
    SELECT 1, 2, '20090901' UNION ALL
    SELECT 2, 9, '20090901'
    GO
    SELECT *
    FROM   dbo.your_table
    GO
    DROP TABLE dbo.your_table
    Last edited by Peso; 01-20-09 at 03:13.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's a clever (confusing too!) implementation Peso, but it still fails on the same issue I mentioned above
    Code:
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
      VALUES (2, 9, '20090101')
    GO
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
      VALUES (2, 7, '20090103')
    GO
    INSERT INTO dbo.your_table (tagid, tagvalue, tagtimestamp)
      VALUES (2, 7, '20090102')
    Unless, of course, these are valid entries! (thinking check constraint only allowing dates >= GetDate()?)
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2009
    Posts
    19
    Thanks for every one. When i started testing, i realised that i should have explained the problem like this.

    The 3 fields in the table are: (Let us ignore the TagKeyID field which is PK and automatically generated).

    TagID TagValue TagTimeStamp

    If

    "The current TagValue for any TagID to be updated = The last updated TagValue for that particular TagID"

    then DO NOT update.

    For example:

    (1, 9, '20090101') - OK
    (1, 7, '20090101') - OK


    (2, 9, '20090101') - OK
    (2, 8, '20090101') - OK
    (2, 8, '20090101') - NOT OK
    (2, 9, '20090101') - OK
    (2, 9, '20090101') - NOT OK

    (2, 8, '20090101') - OK
    (2, 8, '20090102') - NOT OK


    (1, 7, '20090101') - NOT OK (Chk the last updated record for ID 1)
    (1, 9, '20090101') - OK

    ....
    ....

    As i mentioned, the TagKeyID (PK) is automatically generated.

    Sorry, for not expalining the problem correctly. But, i am a novice and hope you all can understand...

    Thanks.
    Last edited by alamsha; 01-20-09 at 09:45.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your example looks flawed to me; these events all happen at 2009-01-01T00:00:00.000 - which means they aren't valid, right?
    I sort of get what you're saying, in which case both solutions posted work in this way, but the issue is when you create records for dates PRIOR to the current record (should this be allowed?).

    (2, 9, '20090101') - OK
    (2, 8, '20090101') - OK
    (2, 8, '20090101') - NOT OK
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2007
    Posts
    183
    Still under work..
    Last edited by Peso; 01-20-09 at 12:12.

  15. #15
    Join Date
    Jan 2009
    Posts
    19
    You are right. In reality the time stamp can never be the same for the same event which has to happen at 2 different times. But different events can happen at the same time.

    None of these fields are unique or PK. A seperate field called TagKey is in the table and unique ID's are auto generated.

    Let me rewrite the sequence like this:
    (1, 9, '20090101') - OK
    (2, 9, '20090101') - OK
    (2, 8, '20090102') - OK

    (2, 8, '20090103') - NOT OK (Eventhough the TimeStamp is different, the TagID and Tagvalues are same for the last time stamp of TagID 2)

    (1, 9, '20090104') - NOT OK (Eventhough the TimeStamp is different, the TagID and Tagvalues are same for the last time stamp of TagID 1)

    (1, 8, '20090104') - OK

    (1, 9, '20090105') - OK (The TagValue is different from the last TimeStamp of TagID 1)

    To Summarise, the TagID and the TagValues should not be the same as in the Last TimeStamp.
    Last edited by alamsha; 01-20-09 at 12:44.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •