Documents history table and Documents triggers.sql

CREATE TABLE `DOCUMENTS_HISTORY` (
  `DOCUMENT_HISTORY_ID`int(11) NOT NULL AUTO_INCREMENT,
  `REVISION_ID`int(11) DEFAULT 0,
  `OP_DESC` varchar(255)  DEFAULT NULL,
  `DOC_ID` int(11) NOT NULL,
  `DOC_CODE` varchar(30) NOT NULL,
  `DOC_PATH` varchar(255) NOT NULL,
  `DOC_UploadedId` int(11) NOT NULL,
  `DOC_Uploaded` datetime DEFAULT NULL,
  `DOC_Approved` datetime DEFAULT NULL,
  `DOC_DATE` datetime NOT NULL,
  `DOC_NAME` varchar(255) NOT NULL,
  `DOC_DSCR` varchar(255) DEFAULT NULL,
  `DOC_META` varchar(255) DEFAULT NULL,
  `DOC_ABIT` char(8) NOT NULL,
  `USR_ID` int(11) NOT NULL,
  `HCP_ID` int(11) DEFAULT NULL,
  `DOC_TEXT` longtext,
  `EDIT_Who` int(11) NOT NULL,
  `EDIT_Date` datetime NOT NULL,
  `DOC_Input_Type` varchar(128) DEFAULT NULL,
  `FILE_ID` int(10) NOT NULL,
  `CURRENT_MYSQL_USER` varchar(255) NOT NULL,
  `UPDATE_DATE` datetime NOT NULL,
  PRIMARY KEY (`DOCUMENT_HISTORY_ID`)
) ENGINE=ARCHIVE  AUTO_INCREMENT=128563 DEFAULT CHARSET=latin1;


DELIMITER $$
DROP PROCEDURE IF EXISTS INSERT_DOCUMENTS_HISTORY$$
CREATE PROCEDURE INSERT_DOCUMENTS_HISTORY(IN REVISION_ID int(11) , OP_DESC varchar(255), DOC_ID int(11) ,  DOC_ABIT char(8),
 DOC_Approved datetime,DOC_CODE varchar(30),DOC_DATE datetime, DOC_DSCR varchar(255), DOC_Input_Type varchar(128), DOC_META varchar(255), 
 DOC_NAME varchar(255),  DOC_PATH varchar(255), DOC_TEXT longtext, DOC_Uploaded datetime, DOC_UploadedId int(11), EDIT_Date datetime,  
 EDIT_Who int(11), FILE_ID int(10),HCP_ID int(11), USR_ID int(11),  CURRENT_MYSQL_USER varchar(255), UPDATE_DATE datetime )
BEGIN

INSERT LOW_PRIORITY INTO DOCUMENTS_HISTORY (REVISION_ID, OP_DESC,  DOC_ID, DOC_ABIT, DOC_Approved,DOC_CODE,
	DOC_DATE, DOC_DSCR, DOC_Input_Type, DOC_META,DOC_NAME, DOC_PATH, DOC_TEXT, DOC_Uploaded, DOC_UploadedId,
	EDIT_Date, EDIT_Who, FILE_ID, HCP_ID, USR_ID, CURRENT_MYSQL_USER, UPDATE_DATE) 
	
	values(REVISION_ID, OP_DESC, DOC_ID, DOC_ABIT, DOC_Approved, DOC_CODE, DOC_DATE, DOC_DSCR,
		 DOC_Input_Type, DOC_META, DOC_NAME, DOC_PATH, DOC_TEXT, DOC_Uploaded,
		 DOC_UploadedId, EDIT_Date, EDIT_Who, FILE_ID, HCP_ID, USR_ID, USER(), NOW());
END$$
DELIMITER ;


DELIMITER $$
DROP TRIGGER IF EXISTS DOCUMENTS_UPDATE_AUDIT_TRIGGER$$

CREATE TRIGGER DOCUMENTS_UPDATE_AUDIT_TRIGGER 
    BEFORE UPDATE ON DOCUMENTS
    FOR EACH ROW 
    BEGIN
    SET @REVISION_ID = (SELECT MAX(REVISION_ID) FROM DOCUMENTS_HISTORY WHERE DOC_ID = NEW.DOC_ID);
    
    IF (@REVISION_ID != NULL) THEN
    	SET @REVISION_ID = @REVISION_ID + 1;
     ELSE 
		SET @REVISION_ID = 0;	
    END IF;
    CALL INSERT_DOCUMENTS_HISTORY( @REVISION_ID, @OP_DESC, NEW.DOC_ID, NEW.DOC_ABIT, NEW.DOC_Approved, NEW.DOC_CODE, NEW.DOC_DATE, NEW.DOC_DSCR,
		 NEW.DOC_Input_Type, NEW.DOC_META, NEW.DOC_NAME, NEW.DOC_PATH, NEW.DOC_TEXT, NEW.DOC_Uploaded,
		 NEW.DOC_UploadedId, NEW.EDIT_Date, NEW.EDIT_Who, NEW.FILE_ID, NEW.HCP_ID, NEW.USR_ID, USER(), NOW());
         
END$$
DELIMITER ;


DELIMITER $$
DROP TRIGGER IF EXISTS DOCUMENTS_INSERT_AUDIT_TRIGGER$$
CREATE TRIGGER DOCUMENTS_INSERT_AUDIT_TRIGGER 
    AFTER INSERT ON DOCUMENTS
    FOR EACH ROW 
    BEGIN
		CALL INSERT_DOCUMENTS_HISTORY( @REVISION_ID, @OP_DESC, NEW.DOC_ID, NEW.DOC_ABIT, NEW.DOC_Approved, NEW.DOC_CODE, NEW.DOC_DATE, NEW.DOC_DSCR,
		 NEW.DOC_Input_Type, NEW.DOC_META, NEW.DOC_NAME, NEW.DOC_PATH, NEW.DOC_TEXT, NEW.DOC_Uploaded,
		 NEW.DOC_UploadedId, NEW.EDIT_Date, NEW.EDIT_Who, NEW.FILE_ID, NEW.HCP_ID, NEW.USR_ID, USER(), NOW());
   END$$
DELIMITER ;