Forum PHP.pl

DELIMITER $

DROP TRIGGER IF EXISTS `pregen`.`OnUpdateProgramUserData` $
CREATE TRIGGER `pregen`.`OnUpdateProgramUserData` AFTER UPDATE ON program_users
FOR EACH ROW
BEGIN
DECLARE userShow VARCHAR(255);
DECLARE date DATETIME;
SET @date = NOW();
SELECT @user INTO userShow;

IF @userShow IS NULL THEN
SET @userShow = 'SYSTEM';
END IF;

IF @date IS NULL THEN
SET @date = NOW();
END IF;

IF NEW.pu_Name != OLD.pu_Name THEN
INSERT INTO program_users_history VALUES(NULL, OLD.pu_ID, @date, @userShow, 'MODIFY', 'pu_Name', OLD.pu_Name, NEW.pu_Name);
END IF;

IF NEW.pu_Company != OLD.pu_Company THEN
INSERT INTO program_users_history VALUES(NULL, OLD.pu_ID, @date, @userShow, 'MODIFY', 'pu_Company', OLD.pu_Company, NEW.pu_Company);
END IF;

IF NEW.pu_Address != OLD.pu_Address THEN
INSERT INTO program_users_history VALUES(NULL, OLD.pu_ID, @date, @userShow, 'MODIFY', 'pu_Address', OLD.pu_Address, NEW.pu_Address);
END IF;

IF NEW.pu_PostalCode != OLD.pu_PostalCode THEN
INSERT INTO program_users_history VALUES(NULL, OLD.pu_ID, @date, @userShow, 'MODIFY', 'pu_PostalCode', OLD.pu_PostalCode, NEW.pu_PostalCode);
END IF;

IF NEW.pu_City != OLD.pu_City THEN
INSERT INTO program_users_history VALUES(NULL, OLD.pu_ID, @date, @userShow, 'MODIFY', 'pu_City', OLD.pu_City, NEW.pu_City);
END IF;

IF NEW.pu_Country != OLD.pu_Country THEN
INSERT INTO program_users_history VALUES(NULL, OLD.pu_ID, @date, @userShow, 'MODIFY', 'pu_Country', OLD.pu_Country, NEW.pu_Country);
END IF;

IF NEW.pu_Phone != OLD.pu_Phone THEN
INSERT INTO program_users_history VALUES(NULL, OLD.pu_ID, @date, @userShow, 'MODIFY', 'pu_Phone', OLD.pu_Phone, NEW.pu_Phone);
END IF;

IF NEW.pu_Fax != OLD.pu_Fax THEN
INSERT INTO program_users_history VALUES(NULL, OLD.pu_ID, @date, @userShow, 'MODIFY', 'pu_Fax', OLD.pu_Fax, NEW.pu_Fax);
END IF;

IF NEW.pu_Email != OLD.pu_Email THEN
INSERT INTO program_users_history VALUES(NULL, OLD.pu_ID, @date, @userShow, 'MODIFY', 'pu_Email', OLD.pu_Email, NEW.pu_Email);
END IF;

IF NEW.pu_OwnerID != OLD.pu_OwnerID THEN
INSERT INTO program_users_history VALUES(NULL, OLD.pu_ID, @date, @userShow, 'MODIFY', 'pu_OwnerID', OLD.pu_OwnerID, NEW.pu_OwnerID);
END IF;
END $

DELIMITER ;