Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Unanswered: sybase trigger on one table and insert into another table

    I have a problem where there are two records in a table which have the same information. These two records causes a problem later on (4 days or more) when the records are used. The developer will not change the primary key so I have to come up with a solution to identify the problem when it occurs. Supposely, the duplicate records is suppose to clean up afterwards though some times it does not.

    The example:
    Id, ProductId, DateOfIssue
    1, 2, 20100917
    2, 2, 20100917 - trigger the event since same productId and DateofIssue
    3, 1, 20100917
    4, 1, 20100918

    Now, I have to grab the first two records when the second one is created. I thought I could do this by triggering the event then copying the records into a duplicate table. Then run a stored procedure to collect more data in other tables including the time it occurs. Right now, I am just trying to isolate the correct number of records to collect into the second table. I do not want to modify the existing table in any way other than adding a trigger.

    This way I can figure out the behaviour and provide more information to the developers.

    So far, the trigger does not execute properly in the way I expected it work.




    create trigger dupPVers
    on PVersjhu
    for insert as
    declare
    @v_Id ID,
    @v_Id2 ID,
    @v_DateOfIssue1 PPIDATE,
    @v_DateOfIssue2 PPIDATE,
    @v_ProductId1 ID,
    @v_ProductId2 ID
    select @v_Id = P1.Id,
    @v_Id2 = P2.Id,
    @v_DateOfIssue1 = P1.DateOfIssue,
    @v_DateOfIssue2 = P2.DateOfIssue,
    @v_ProductId1 = P1.ProductId,
    @v_ProductId2 = P2.ProductId
    from PVersjhu P1,
    PVersjhu P2
    where P1.Id != P2.Id
    and P1.ProductId = P2.ProductId
    and P1.DateOfIssue = P2.DateOfIssue
    begin
    insert into dupPVersjhu (FirstProductId, SecondProductId, FirstId, SecondId, firstDateOfIssue, secondDateOfIssue)
    values (@v_ProductId1, @v_ProductId2, @v_Id, @v_Id2, @v_DateOfIssue1,@v_DateOfIssue2)
    end


    Tables:

    create table dupPVersjhu
    ( ProductId ID null,
    DateOfIssue DATE null,
    UserId ID null
    )

    create table PVersjhu
    ( Id ID null,
    ProductId ID null,
    DateOfIssue DATE null,
    )

  2. #2
    Join Date
    Sep 2010
    Posts
    3
    no help? Anyone?

  3. #3
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    It's not clear to me what it is supposed to do. When an insert occurs on PVersjhu, it can be a duplicate? And if so, what should happen to the inserted record?

    The trigger you posted does not contain any cleanup (delete), and therefore will not fix he duplicate record-issue.
    I'm not crazy, I'm an aeroplane!

  4. #4
    Join Date
    Sep 2010
    Posts
    3
    Quote Originally Posted by Martijnvs View Post
    It's not clear to me what it is supposed to do. When an insert occurs on PVersjhu, it can be a duplicate? And if so, what should happen to the inserted record?

    The trigger you posted does not contain any cleanup (delete), and therefore will not fix he duplicate record-issue.

    I cannot do any cleanup at this time. I simply need to record the event since the records are inserted, updated and deleted. I think i figured out what I need to do:

    First select the records then insert the id into another table. Then on the other table create a trigger to call stp to insertion call another upon update and another upon deletion. Each stp will handle filling the fields.

    There are client logs and right now we are not sure which client is causing this problem. I am trying to narrow down who and when the even occurs and determine what the pattern is. This way, we can isolate the problem. Unfortunately, the problem cannot be solved by rolling back the transaction or changing the primary keys.

Posting Permissions

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