Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    30

    Unanswered: Trigger generates strange behavior, want to know if data corruption is possible

    Hey All,
    Have some strange behavior going on and I want to confirm that it is either alright or not. I have a trigger on one of my tables that will eventually exist on all tables. It's purpose is to monitor user activity and log all data changes in a separate table. This trigger works wells, my problem exist here: When I add a new record to the table the trigger exist on and tab to the next line, the record ID (auto increment identity key) which (for example) should become 96 does not, the record id takes the value of the new record being added to the tblLogging table. And even funnier is that when it adopts this number, the entire record's values for each col'm change to mirror those of the already existing record in said table. IE/I add a new record to table Vehicle, should be record number 96 - The new record number in tblLogging is 69 - When arrow down to next line the entire record changes to record number 69 with all same values of existing record 69 in table Vehicle.
    However, having said that, I can then run the code and the new record I added is there with the new the correct value. There has been no duplication. This behavior while non-problematic now seems inappropriate. Below is the trigger code....The execution commits the addition, but I am at a loss as to why it is recreating an existing record if even momentarily...
    Looking for comments.

    CREATE TRIGGER tr_TrackingUsers_Vehicles
    ON dbo.Vehicles
    FOR INSERT, UPDATE, DELETE
    AS

    DECLARE @action VARCHAR(23)
    IF EXISTS(SELECT * FROM inserted)
    IF EXISTS(SELECT * FROM deleted)
    SET @action = 'Updated existing record'
    ELSE
    SET @action = 'New Record Added'
    ELSE
    SET @action = 'Deleted a record'

    IF @action = 'New Record Added'
    INSERT INTO tblLogging(OrderID, NewQty, OldQty, DateModified, ActionTaken,UserName, TableName)
    SELECT i.ID, '(Date) ' + CAST(ISNULL(i.[Date], ' ') AS VARCHAR(20)) + ' : (Test Year) ' + CAST(ISNULL( i.[Test Year], ' ') AS VARCHAR(4)) + ' : (Number) '+ CAST(ISNULL(i.Number, ' ')AS VARCHAR(8))+ ' : (Model Year) '+ CAST(ISNULL(i.[Model Year], ' ') AS VARCHAR(4)) + ' : (Model) '+ CAST(ISNULL(i.Model, ' ') AS VARCHAR(4)) + ' : ( Model Detail) '+ CAST(ISNULL(i.[Model Detail], ' ') AS VARCHAR(5)) + ' : (Owner) '+ CAST(ISNULL(i.Owner, ' ' ) AS VARCHAR(3)) + ' : (Comments) '+ CAST(ISNULL(i.Comments, ' ') AS VARCHAR(255)) + ' : (Category) '+ CAST(ISNULL(i.Category, ' ') AS VARCHAR(2)) + ' : (Emission Level) '+ CAST(ISNULL(i.[Emission Level], ' ') AS VARCHAR(3)) + ' : (VIN) '+ CAST(ISNULL(i.VIN, ' ') AS VARCHAR(12))+ ' : (Fuel) ' + CAST(ISNULL(i.Fuel, ' ') AS VARCHAR(3))+ ' : (Test Weight) ' + CAST(ISNULL(i.[Test Weight], ' ') AS VARCHAR(10)) + ' : (A) ' + CAST(ISNULL(i.A, ' ') AS VARCHAR(3)) + ' : (B) ' + CAST(ISNULL(i.B, ' ') AS VARCHAR(3)) + ' : (C) ' + CAST(ISNULL(i.C, ' ') AS VARCHAR(3)),ISNULL(NULL, ' '), GETDATE(), @action, SUSER_SNAME(), 'Vehicles'
    FROM inserted i
    ELSE
    IF @action = 'Deleted a record'
    INSERT INTO tblLogging(OrderID, NewQty, OldQty,DateModified, ActionTaken,UserName, TableName)
    SELECT d.ID,ISNULL(NULL, ' '), '(Date) ' + CAST(ISNULL(d.[Date], ' ') AS VARCHAR(20)) + ' : (Test Year) ' + CAST(ISNULL( d.[Test Year], ' ') AS VARCHAR(4)) + ' : (Number) '+ CAST(ISNULL(d.Number, ' ')AS VARCHAR(8))+ ' : (Model Year) '+ CAST(ISNULL(d.[Model Year], ' ') AS VARCHAR(4)) + ' : (Model) '+ CAST(ISNULL(d.Model, ' ') AS VARCHAR(4)) + ' : ( Model Detail) '+ CAST(ISNULL(d.[Model Detail], ' ') AS VARCHAR(5)) + ' : (Owner) '+ CAST(ISNULL(d.Owner, ' ' ) AS VARCHAR(3)) + ' : (Comments) '+ CAST(ISNULL(d.Comments, ' ') AS VARCHAR(255)) + ' : (Category) '+ CAST(ISNULL(d.Category, ' ') AS VARCHAR(2)) + ' : (Emission Level) '+ CAST(ISNULL(d.[Emission Level], ' ') AS VARCHAR(3)) + ' : (VIN) '+ CAST(ISNULL(d.VIN, ' ') AS VARCHAR(12))+ ' : (Fuel) ' + CAST(ISNULL(d.Fuel, ' ') AS VARCHAR(3))+ ' : (Test Weight) ' + CAST(ISNULL(d.[Test Weight], ' ') AS VARCHAR(10)) + ' : (A) ' + CAST(ISNULL(d.A, ' ') AS VARCHAR(3)) + ' : (B) ' + CAST(ISNULL(d.B, ' ') AS VARCHAR(3)) + ' : (C) ' + CAST(ISNULL(d.C, ' ') AS VARCHAR(3)), GETDATE(), @action, SUSER_SNAME(), 'Vehicles'
    FROM deleted d
    ELSE
    INSERT INTO tblLogging(OrderID, NewQty, OldQty, DateModified, ActionTaken,UserName, TableName)
    SELECT i.ID, '(Date) ' + CAST(ISNULL(i.[Date], ' ') AS VARCHAR(20)) + ' : (Test Year) ' + CAST(ISNULL( i.[Test Year], ' ') AS VARCHAR(4)) + ' : (Number) '+ CAST(ISNULL(i.Number, ' ')AS VARCHAR(8))+ ' : (Model Year) '+ CAST(ISNULL(i.[Model Year], ' ') AS VARCHAR(4)) + ' : (Model) '+ CAST(ISNULL(i.Model, ' ') AS VARCHAR(4)) + ' : ( Model Detail) '+ CAST(ISNULL(i.[Model Detail], ' ') AS VARCHAR(5)) + ' : (Owner) '+ CAST(ISNULL(i.Owner, ' ' ) AS VARCHAR(3)) + ' : (Comments) '+ CAST(ISNULL(i.Comments, ' ') AS VARCHAR(255)) + ' : (Category) '+ CAST(ISNULL(i.Category, ' ') AS VARCHAR(2)) + ' : (Emission Level) '+ CAST(ISNULL(i.[Emission Level], ' ') AS VARCHAR(3)) + ' : (VIN) '+ CAST(ISNULL(i.VIN, ' ') AS VARCHAR(12))+ ' : (Fuel) ' + CAST(ISNULL(i.Fuel, ' ') AS VARCHAR(3))+ ' : (Test Weight) ' + CAST(ISNULL(i.[Test Weight], ' ') AS VARCHAR(10)) + ' : (A) ' + CAST(ISNULL(i.A, ' ') AS VARCHAR(3)) + ' : (B) ' + CAST(ISNULL(i.B, ' ') AS VARCHAR(3)) + ' : (C) ' + CAST(ISNULL(i.C, ' ') AS VARCHAR(3)), '(Date) ' + CAST(ISNULL(d.[Date], ' ') AS VARCHAR(20)) + ' : (Test Year) ' + CAST(ISNULL( d.[Test Year], ' ') AS VARCHAR(4)) + ' : (Number) '+ CAST(ISNULL(d.Number, ' ')AS VARCHAR(8))+ ' : (Model Year) '+ CAST(ISNULL(d.[Model Year], ' ') AS VARCHAR(4)) + ' : (Model) '+ CAST(ISNULL(d.Model, ' ') AS VARCHAR(4)) + ' : ( Model Detail) '+ CAST(ISNULL(d.[Model Detail], ' ') AS VARCHAR(5)) + ' : (Owner) '+ CAST(ISNULL(d.Owner, ' ' ) AS VARCHAR(3)) + ' : (Comments) '+ CAST(ISNULL(d.Comments, ' ') AS VARCHAR(255)) + ' : (Category) '+ CAST(ISNULL(d.Category, ' ') AS VARCHAR(2)) + ' : (Emission Level) '+ CAST(ISNULL(d.[Emission Level], ' ') AS VARCHAR(3)) + ' : (VIN) '+ CAST(ISNULL(d.VIN, ' ') AS VARCHAR(12))+ ' : (Fuel) ' + CAST(ISNULL(d.Fuel, ' ') AS VARCHAR(3))+ ' : (Test Weight) ' + CAST(ISNULL(d.[Test Weight], ' ') AS VARCHAR(10)) + ' : (A) ' + CAST(ISNULL(d.A, ' ') AS VARCHAR(3)) + ' : (B) ' + CAST(ISNULL(d.B, ' ') AS VARCHAR(3)) + ' : (C) ' + CAST(ISNULL(d.C, ' ') AS VARCHAR(3)), GETDATE(), @action, SUSER_SNAME(), 'Vehicles'
    FROM inserted i INNER JOIN deleted d ON i.[ID] = d.[ID]

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Make sure you use BEGIN..END with your IF statements. You do not need them if your IF statement block only executes 1 line of code
    Code:
    IF (@A = 1) 
     SET @B=2
    However more than 1 line of code you need BEGIN...END
    Code:
    IF (@A = 1) BEGIN 
      SET @B = 2 
      UPDATE tt SET col1 = col2*x 
    END
    For you
    IF EXISTS(SELECT * FROM inserted)
    IF EXISTS(SELECT * FROM deleted)
    SET @action = 'Updated existing record'
    ELSE
    SET @action = 'New Record Added'
    ELSE
    SET @action = 'Deleted a record'

    Should be
    Code:
    IF EXISTS(SELECT * FROM inserted) BEGIN
       IF EXISTS(SELECT * FROM deleted)
          SET @action = 'Updated existing record'
       ELSE
          SET @action = 'New Record Added'
    END
    ELSE
       SET @action = 'Deleted a record'
    MCDBA

  3. #3
    Join Date
    Jul 2002
    Posts
    30

    Talking

    Thank You. This fixed the problem perfectly.
    SE

  4. #4
    Join Date
    Jul 2002
    Posts
    30

    Unhappy

    I am experiencing the same problem as above with same trigger, but on different table ---Why? Can you tell me?


    CREATE TRIGGER tr_TrackingUsers_VehicleCoefficients
    ON dbo.[Vehicle Coefficients]
    FOR INSERT, UPDATE, DELETE
    AS

    DECLARE @action VARCHAR(23)
    IF EXISTS(SELECT * FROM inserted) BEGIN
    IF EXISTS(SELECT * FROM deleted)
    SET @action = 'Updated existing record'
    ELSE
    SET @action = 'New Record Added'
    END
    ELSE
    SET @action = 'Deleted a record'

    IF @action = 'New Record Added'
    BEGIN
    INSERT INTO tblLogging(OrderID, NewQty, OldQty, DateModified, ActionTaken,UserName, TableName)
    SELECT i.ID, '(Vehicle ID) ' + CAST(ISNULL(i.[Vehicle ID], ' ') AS VARCHAR(4)) + ' : (Dyno) ' + CAST(ISNULL( i.[Dyno], ' ') AS VARCHAR(4)) + ' : (Temperature) '+ CAST(ISNULL(i.Temperature, ' ')AS VARCHAR(4)) + ' : (A) ' + CAST(ISNULL(i.A, ' ') AS VARCHAR(3)) + ' : (B) ' + CAST(ISNULL(i.B, ' ') AS VARCHAR(3)) + ' : (C) ' + CAST(ISNULL(i.C, ' ') AS VARCHAR(3)),ISNULL(NULL, ' '), GETDATE(), @action, SUSER_SNAME(), 'Vehicle Coefficients'
    FROM inserted i
    END
    IF @action = 'Deleted a record'
    BEGIN
    INSERT INTO tblLogging(OrderID, NewQty, OldQty,DateModified, ActionTaken,UserName, TableName)
    SELECT d.ID,ISNULL(NULL, ' '), '(Vehicle ID) ' + CAST(ISNULL(d.[Vehicle ID], ' ') AS VARCHAR(4)) + ' : (Dyno) ' + CAST(ISNULL( d.[Dyno], ' ') AS VARCHAR(4)) + ' : (Temperature) '+ CAST(ISNULL(d.Temperature, ' ')AS VARCHAR(4)) + ' : (A) ' + CAST(ISNULL(d.A, ' ') AS VARCHAR(3)) + ' : (B) ' + CAST(ISNULL(d.B, ' ') AS VARCHAR(3)) + ' : (C) ' + CAST(ISNULL(d.C, ' ') AS VARCHAR(3)), GETDATE(), @action, SUSER_SNAME(), 'Vehicle Coefficients'
    FROM deleted d
    END
    ELSE
    BEGIN
    INSERT INTO tblLogging(OrderID, NewQty, OldQty, DateModified, ActionTaken,UserName, TableName)
    SELECT i.ID, '(Vehicle ID) ' + CAST(ISNULL(i.[Vehicle ID], ' ') AS VARCHAR(4)) + ' : (Dyno) ' + CAST(ISNULL( i.[Dyno], ' ') AS VARCHAR(4)) + ' : (Temperature) '+ CAST(ISNULL(i.Temperature, ' ')AS VARCHAR(4)) + ' : (A) ' + CAST(ISNULL(i.A, ' ') AS VARCHAR(3)) + ' : (B) ' + CAST(ISNULL(i.B, ' ') AS VARCHAR(3)) + ' : (C) ' + CAST(ISNULL(i.C, ' ') AS VARCHAR(3)), '(Vehicle ID) ' + CAST(ISNULL(d.[Vehicle ID], ' ') AS VARCHAR(4)) + ' : (Dyno) ' + CAST(ISNULL( d.[Dyno], ' ') AS VARCHAR(4)) + ' : (Temperature) '+ CAST(ISNULL(d.Temperature, ' ')AS VARCHAR(4)) + ' : (A) ' + CAST(ISNULL(d.A, ' ') AS VARCHAR(3)) + ' : (B) ' + CAST(ISNULL(d.B, ' ') AS VARCHAR(3)) + ' : (C) ' + CAST(ISNULL(d.C, ' ') AS VARCHAR(3)), GETDATE(), @action, SUSER_SNAME(), 'Vehicle Coefficients'
    FROM inserted i INNER JOIN deleted d ON i.[ID] = d.[ID]
    END

  5. #5
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    It's in your second IF block

    You have
    Code:
    IF @action = 'New Record Added'  BEGIN
        ..
        ..
    END
    
    IF @action = 'Deleted a record'  BEGIN
        ..
        ..
    END
    ELSE 
    BEGIN
        ..
        ..
    END
    If @action is set to 'New Record Added' , you will get 2 inserts, the first on @action = 'New Record Added' and the second on the ELSE for 'Deleted a record' .

    Should be
    Code:
    IF @action = 'New Record Added'  BEGIN
        ..
        ..
    END
    
    IF @action = 'Deleted a record'  BEGIN
        ..
        ..
    END
    
    IF  @action = 'Updated existing record' BEGIN
        ..
        ..
    END
    MCDBA

  6. #6
    Join Date
    Jul 2002
    Posts
    30

    Talking

    Again, thank you. I didn't catch it because that very same statement works on the other tables...But I should have opened my eyes.
    Thanks

Posting Permissions

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