Sample MySQL 5.0 Trigger for Insert Statement with IF Condition – DocuWare

Here I am updating the values that are been inserted into a table called “dwdata2.hr_mysql_ftm” every time a new row gets inserted.

This is a sample trigger from a DocuWare system. The idea is to be able to search via fulltext retrieval but only from the meta data (file cabinet field data). This trigger will change the flags of the fulltext table so that the next time the fulltext job runs against the file cabinet it will only extract the index data.

STOP

STOP

NOTE: THIS IS A SQL STATEMENT AND IF YOU MISS-CONFIGURE OR TYPE INCORRECTLY, IT COULD CAUSE SERIOUS DAMAGE TO YOUR DATABASE, TAKING A FULL BACK UP OF THE DATABASE MAY PREVENT THESE DAMAGES. WE (DOCUWARE, BLOGGER, THIS SITE) ARE NOT RESPONSIBLE FOR ANY DAMAGES. DO IT AT YOUR OWN RISK.

Sample SQL Script Trigger for MySQL Database:

DELIMITER //
CREATE TRIGGER dwdata2.tr_iChangeFTMStatus_HR
BEFORE INSERT ON dwdata2.hr_mysql_ftm
FOR EACH ROW
BEGIN
/** Check if one page or multiple pages ***/
IF NEW.PAGE = 0 THEN
/** Update the data been inserted **/
SET NEW.ROW_STATE = 'REINDEXM';
END IF;
IF NEW.PAGE > 0 THEN
/** Update for additional pages **/
SET NEW.ROW_STATE = 'INDEXED';
END IF;
END;
//



Tested under DocuWare 5.1b – SP3.