Skip to content

Instantly share code, notes, and snippets.

@sudarshan-webonise

sudarshan-webonise/Documents history table and Documents triggers.sql

Created July 21, 2015 11:15
Show Gist options
  • Save sudarshan-webonise/95ba6a1c2cabe13c4b63 to your computer and use it in GitHub Desktop.
Save sudarshan-webonise/95ba6a1c2cabe13c4b63 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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 ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment