[SOLVED] MYSQL History Trigger

[acadia]

Hi Everyone,

 

I am trying to create a history trigger so that when I change data in one field it adds to the history table with the old data. 

 

This is what I have so far:

 

CREATE TRIGGER StaffUpdate BEFORE UPDATE ON Staff
    FOR EACH ROW
    BEGIN
        INSERT IGNORE INTO StaffHistory
        (
            StaffID,
            FirstName,
            MiddleName,
            Surname,
            PositionTitle,
            IDNumber,
            Address,
            StreetAddress,
            PostCode,
            City,
            PhoneNumber,
            HomeNumber,
            MobileNumber,
            eMail,
            IRDNumber,
            EmploymentDate,
            RetirementDate,
            Password,
            Username,
            AdminStaff,
            AdminStudent,
            AdminTrainers,
            AdminCourses,
            AdminProviders,
            LastChangedBy,
            LastChangedDate
        )
        VALUES
        (
            OLD.StaffID,
            OLD.FirstName,
            OLD.MiddleName,
            OLD.Surname,
            OLD.PositionTitle,
            OLD.IDNumber,
            OLD.Address,
            OLD.StreetAddress,
            OLD.PostCode,
            OLD.City,
            OLD.PhoneNumber,
            OLD.HomeNumber,
            OLD.MobileNumber,
            OLD.eMail,
            OLD.IRDNumber,
            OLD.EmploymentDate,
            OLD.RetirementDate,
            OLD.Password,
            OLD.Username,
            OLD.AdminStaff,
            OLD.AdminStudent,
            OLD.AdminTrainers,
            OLD.AdminCourses,
            OLD.AdminProviders,
            OLD.LastChangedBy,
            OLD.LastChangedDate
        );
    END

 

At present it does save the last changed date but it doesn't save the data. 

 

If you have any help it would be really appreciated.

 

Regards,

Chris

[Maq]

I'm pretty certain you need to replace this line:

 

        INSERT IGNORE INTO StaffHistory

 

with this:

 

        REPLACE INTO StaffHistory

 

REPLACE INTO will either update a record if it's already there, or create (insert) a new record if it doesn't exist.

 

* NOT TESTED - Back up before trying this *

[acadia]

Hi Maq,

 

Thank you for you imput but I have relised that the sql was correct and it was my PHP that was incorrect.  On the PHP output page I had listed it from StaffID rather than StaffHistoryID. My bad!

 

Thanks again for looking though.

 

Chris

[Maq]

Hi Maq,

 

Thank you for you imput but I have relised that the sql was correct and it was my PHP that was incorrect.  On the PHP output page I had listed it from StaffID rather than StaffHistoryID. My bad!

 

Thanks again for looking though.

 

Chris

 

No problem.  So you have resolved the issue, and everything is working properly now?

[acadia]

Yes Maq its working well now.  Thanks for helping. 

 

It now displays every changed detail on the output page as it should.

 

Chris