Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2014
    Posts
    7

    Unanswered: Problem with AFTER UPDATE Trigger

    I want to log changes in a table and write them into a Log table using AFTER INSERT, BEFORE DELETE and AFTER UPDATE triggers.
    The thing is that the AFTER UPDATE trigger works strange here. When I update more than one column in a certain record the trigger writes the old values into the log table but it works fine if I just update one column.
    I reduced it to a test scenario with a small table A and a log table A_Log.


    Initialization
    INSERT INTO A (RecordID, Value_1, Value_2, Value_3) VALUES (0, 1, 1, 1)#

    Update 1:

    Code:
    
    
    UPDATE A
    SET 
    	Value_1 = 2#
    
       RecordID   Modification              TimeOfChange   OrgRecordID       Value_1       Value_2       Value_3
    ===========   ============   =======================   ===========   ===========   ===========   ===========
              6   U                 1/22/2015 2:47:45 PM             2             2             1             1
    
    
    Great, as expected.


    Update 2:

    Code:
    
    
    UPDATE A
    SET 
    	Value_1 = 2
    	,Value_2 = 2#
    	
    	
    
       RecordID   Modification              TimeOfChange   OrgRecordID       Value_1       Value_2       Value_3
    ===========   ============   =======================   ===========   ===========   ===========   ===========
              7   U                 1/22/2015 2:49:26 PM             2             2             1             1
    
    
    Result is not as expected. Value_2 is still 1, whereas it should be 2. It is, of course, in the main table A.

    So, where am I wrong? Any help is very much appreciated.



    For a closer look, here is the DDL:

    Code:
    
    
    Create Table A (
         RecordID              INTEGER NOT NULL,
         Value_1               INTEGER,
         Value_2               INTEGER,
         Value_3               INTEGER
    )#
    
    Create Table A_Log (
         RecordID              INTEGER NOT NULL,
         Modification          CHAR(1), --I U D
         TimeOfChange          TIMESTAMP,
         OrgRecordID           INTEGER,
         Value_1               INTEGER,
         Value_2               INTEGER,
         Value_3               INTEGER
    )#
    
    CREATE TRIGGER TRG_A_AI0 
    AFTER INSERT ON A
    FOR EACH ROW
    INSERT INTO A_Log(
    	RecordID,
    	Modification,
    	TimeOfChange,
    	OrgRecordID,
    	Value_1,
    	Value_2,
    	Value_3
    ) VALUES (
    	0,
    	'I',
    	CURRENT_TIMESTAMP(),
    	Old.RecordID,
    	Old.Value_1,
    	Old.Value_2,
    	Old.Value_3
    )#
    
    
    -- AFTER UPDATE
    CREATE TRIGGER TRG_A_AU0 
    AFTER UPDATE ON A
    FOR EACH ROW
    INSERT INTO A_Log(
    	RecordID,
    	Modification,
    	TimeOfChange,
    	OrgRecordID,
    	Value_1,
    	Value_2,
    	Value_3
    ) VALUES (
    	0,
    	'U',
    	CURRENT_TIMESTAMP(),
    	New.RecordID,
    	New.Value_1,
    	New.Value_2,
    	New.Value_3
    )#
    
    
    
    -- BEFORE DELETE
    CREATE TRIGGER TRG_A_BD0 
    BEFORE DELETE ON A
    FOR EACH ROW
    INSERT INTO A_Log(
    	RecordID,
    	Modification,
    	TimeOfChange,
    	OrgRecordID,
    	Value_1,
    	Value_2,
    	Value_3
    ) VALUES (
    	0,
    	'D',
    	CURRENT_TIMESTAMP(),
    	New.RecordID,
    	New.Value_1,
    	New.Value_2,
    	New.Value_3
    )#
    
    TIA,
    Joe

  2. #2
    Join Date
    Feb 2014
    Posts
    7

    Problem with AFTER UPDATE Trigger, updating multiple columns

    Hi.

    I hope this isn't a duplicate posting, but I don't see the posting of this issue I did half an hiur ago.

    I want to log changes in a table and write them into a Log table using AFTER INSERT, BEFORE DELETE and AFTER UPDATE triggers.
    The thing is that the AFTER UPDATE trigger works strange here. When I update more than one column in a certain record the trigger writes the old values into the log table but it works fine if I just update one column.
    I reduced it to a test scenario with a small table A and a log table A_Log.


    Initialization
    INSERT INTO A (RecordID, Value_1, Value_2, Value_3) VALUES (0, 1, 1, 1)#

    Update 1:

    Code:
    UPDATE A
    SET 
         Value_1 = 2#
    
       RecordID   Modification              TimeOfChange   OrgRecordID       Value_1       Value_2       Value_3
    ===========   ============   =======================   ===========   ===========   ===========   ===========
              6   U                 1/22/2015 2:47:45 PM             2             2             1             1
    Great, as expected.


    Update 2:

    Code:
    UPDATE A
    SET 
    	Value_1 = 2
    	,Value_2 = 2#
    	
    	
    
       RecordID   Modification              TimeOfChange   OrgRecordID       Value_1       Value_2       Value_3
    ===========   ============   =======================   ===========   ===========   ===========   ===========
              7   U                 1/22/2015 2:49:26 PM             2             2             1             1
    Result is not as expected. Value_2 is still 1, whereas it should be 2. It is, of course, in the main table A.

    So, where am I wrong? Any help is very much appreciated.



    For a closer look, here is the DDL:

    Code:
    Create Table A (
         RecordID              INTEGER NOT NULL,
         Value_1               INTEGER,
         Value_2               INTEGER,
         Value_3               INTEGER
    )#
    
    Create Table A_Log (
         RecordID              INTEGER NOT NULL,
         Modification          CHAR(1), --I U D
         TimeOfChange          TIMESTAMP,
         OrgRecordID           INTEGER,
         Value_1               INTEGER,
         Value_2               INTEGER,
         Value_3               INTEGER
    )#
    
    CREATE TRIGGER TRG_A_AI0 
    AFTER INSERT ON A
    FOR EACH ROW
    INSERT INTO A_Log(
    	RecordID,
    	Modification,
    	TimeOfChange,
    	OrgRecordID,
    	Value_1,
    	Value_2,
    	Value_3
    ) VALUES (
    	0,
    	'I',
    	CURRENT_TIMESTAMP(),
    	Old.RecordID,
    	Old.Value_1,
    	Old.Value_2,
    	Old.Value_3
    )#
    
    
    -- AFTER UPDATE
    CREATE TRIGGER TRG_A_AU0 
    AFTER UPDATE ON A
    FOR EACH ROW
    INSERT INTO A_Log(
    	RecordID,
    	Modification,
    	TimeOfChange,
    	OrgRecordID,
    	Value_1,
    	Value_2,
    	Value_3
    ) VALUES (
    	0,
    	'U',
    	CURRENT_TIMESTAMP(),
    	New.RecordID,
    	New.Value_1,
    	New.Value_2,
    	New.Value_3
    )#
    
    
    
    -- BEFORE DELETE
    CREATE TRIGGER TRG_A_BD0 
    BEFORE DELETE ON A
    FOR EACH ROW
    INSERT INTO A_Log(
    	RecordID,
    	Modification,
    	TimeOfChange,
    	OrgRecordID,
    	Value_1,
    	Value_2,
    	Value_3
    ) VALUES (
    	0,
    	'D',
    	CURRENT_TIMESTAMP(),
    	New.RecordID,
    	New.Value_1,
    	New.Value_2,
    	New.Value_3
    )#
    TIA,
    Joe

  3. #3
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    What version of PSQL are you using? I tried your statements with PSQL V11.30 and it worked correctly. After the second Update, A_Log had the correct values for both columns that were updated.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  4. #4
    Join Date
    Feb 2014
    Posts
    7
    Hi.

    Quote Originally Posted by mirtheil View Post
    What version of PSQL are you using? I tried your statements with PSQL V11.30 and it worked correctly.
    Sorry, I forgot to mention it.

    PSQL 9, Control Center About dialog says 9.50.077.002.

    bye,
    Joe

  5. #5
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Let me see if I have a PSQL v9 install I can test on. Mainly I have v11 and V12.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    What about the version of W3ODBCEI.DLL an W3ODBCCI.DLL? The PCC About only shows the version of the PCC and not the engine.
    If those DLLs still show 9.50, you might try updating to v9 SP3 Update 4 (9.71). It's available at http://www.pervasive.com/database/Ho...ts/PSQLv9.aspx. If that doesn't help, you might have to upgrade to v11 or v12 since v9 hasn't been supported since Jan. 1 2010 (as documented at http://www.pervasive.com/database/Ho...Lifecycle.aspx).
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  7. #7
    Join Date
    Feb 2014
    Posts
    7
    The version of W3ODBCEI.DLL is 9.50.77.2. So, if I got you right an update could be an idea. Still I'm a bit surprised that such an essential function should be buggy.

    Anyway, an update has to be permitted by my customer. :-)
    I'll report.

  8. #8
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Yes, I would update to the 9.71 release. 9.50 was v9 SP2. I would suggest updating in a test environment before updating the production system.
    Triggers have never been first class citizens in the PSQL world. This is mainly because enabling a Trigger disables Btrieve access for that operation. For example, an Insert trigger will cause a Btrieve application issuing an Insert to return a status 149 and the Btrieve level insert fails.

    The ideal option for logging changes would be AuditMaster but that's not available for v9 from Actian any longer.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  9. #9
    Join Date
    Feb 2014
    Posts
    7
    Everything's fine now, 9.71 did the job. Thank you, you made my week. I was always too sure about that the problem sat in front of the monitor.

    Bye,
    Joe

  10. #10
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Glad to hear it. Just remember that v9 is unsupported so if something does come up in the future, you'll probably want to consider upgrading.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

Tags for this Thread

Posting Permissions

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