CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `mkt_assist_status_after_insert` AFTER INSERT ON `mkt_assist_status` FOR EACH ROW begin DECLARE vUser varchar(50); DECLARE pname varchar(100); IF(NEW.updated_by='0')THEN set vUser='Admin'; else set vUser=(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.updated_by); END IF; IF((select assist_ref_table from mkt_assist where idmkt_assist = NEW.idmkt_assist)='Employee')THEN set pname =(select concat(firstname, ' ',lastname) from emp where emp_id = (select assist_ref_id from mkt_assist where idmkt_assist = NEW.idmkt_assist)); else set pname = (select concat(prosp_first_name, ' ',prosp_last_name) from prosp_consult where prosp_consult_id = (select assist_ref_id from mkt_assist where idmkt_assist = NEW.idmkt_assist)); END IF; INSERT INTO log_global set last_update = now(), updater = vUser, table_update = 'mkt_assist_status', actions = 'A', table_id = CONCAT('idmkt_assist_status', '.',NEW.idmkt_assist_status, '\n','idmkt_assist', '.',NEW.idmkt_assist), col1 = pname, col2 = CONCAT('Ref_table', '.',(select assist_ref_table from mkt_assist where idmkt_assist = NEW.idmkt_assist)), col4 = CONCAT('Status', '.',(select look_values from look_values where look_values_id=NEW.assist_status)), col5 = CONCAT('Company', '.',(select companyname from mkt_company where idmkt_company = (select compname from mkt_open where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = NEW.idmkt_assist)))), col7 = CONCAT('Client', '.',(select companyname from mkt_company where idmkt_company= (select client_name from mkt_open where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = NEW.idmkt_assist)))), col8 = CONCAT('Location', '.',(select concat(look_city .city_name,',',look_state .state_name) from look_city inner join look_state on look_city.state_id=look_state.state_id where city_id= (select location from mkt_open where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = NEW.idmkt_assist)))), col14 = CONCAT('Rate', '.',(select assist_rate from mkt_assist where idmkt_assist = NEW.idmkt_assist)), col15 = CONCAT('Days', '.',NEW.no_days), col10 = CONCAT('Start_date', '.',date_format(NEW.stat_st_dt,'%m-%d-%y')), col11 = CONCAT('End_date', '.',date_format(NEW.stat_e_dt,'%m-%d-%y')), col13 = CONCAT('Summary', '.',(select summary from mkt_assist where idmkt_assist = NEW.idmkt_assist)); END CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `mkt_assist_status_after_update` AFTER UPDATE ON `mkt_assist_status` FOR EACH ROW begin DECLARE pname varchar(100); DECLARE oldmkt_status int(5); DECLARE oldstat_st_dt date; DECLARE oldstat_e_dt date; DECLARE oldno_days int(5); IF OLD.assist_status IS NULL THEN SET oldmkt_status = ''; ELSE SET oldmkt_status = OLD.assist_status; END IF; IF OLD.stat_st_dt IS NULL THEN SET oldstat_st_dt = ''; ELSE SET oldstat_st_dt = OLD.stat_st_dt; END IF; IF OLD.stat_e_dt IS NULL THEN SET oldstat_e_dt = ''; ELSE SET oldstat_e_dt = OLD.stat_e_dt; END IF; IF OLD.no_days IS NULL THEN SET oldno_days = ''; ELSE SET oldno_days = OLD.no_days; END IF; IF((select assist_ref_table from mkt_assist where idmkt_assist = OLD.idmkt_assist)='Employee')THEN set pname =(select concat(firstname, ' ',lastname) from emp where emp_id = (select assist_ref_id from mkt_assist where idmkt_assist = OLD.idmkt_assist)); else set pname = (select concat(prosp_first_name, ' ',prosp_last_name) from prosp_consult where prosp_consult_id = (select assist_ref_id from mkt_assist where idmkt_assist = OLD.idmkt_assist)); END IF; IF((NEW.assist_status!=oldmkt_status)OR(NEW.stat_st_dt!=oldstat_st_dt)OR(NEW.stat_e_dt!=oldstat_e_dt)OR(NEW.no_days!=oldno_days))THEN INSERT INTO log_global set last_update = now(), updater = (select concat(firstname,' ',lastname) as name from emp where emp_id=(case when (OLD.updated_by = 0) then 1 else OLD.updated_by end)), table_update = 'mkt_assist_status', actions = 'O', table_id = CONCAT('idmkt_assist_status', '.',OLD.idmkt_assist_status, '\n','idmkt_assist', '.',OLD.idmkt_assist), col1 = pname, col2 = CONCAT('Ref_table', '.',(select assist_ref_table from mkt_assist where idmkt_assist = OLD.idmkt_assist)), col4 = CONCAT('Status', '.',(select look_values from look_values where look_values_id=OLD.assist_status)), col5 = CONCAT('Company', '.',(select companyname from mkt_company where idmkt_company = (select compname from mkt_open where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = OLD.idmkt_assist)))), col7 = CONCAT('Client', '.',(select companyname from mkt_company where idmkt_company= (select client_name from mkt_open where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = OLD.idmkt_assist)))), col8 = CONCAT('Location', '.',(select concat(look_city .city_name,',',look_state .state_name) from look_city inner join look_state on look_city.state_id=look_state.state_id where city_id= (select location from mkt_open where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = OLD.idmkt_assist)))), col10 = CONCAT('Start_date', '.',date_format(OLD.stat_st_dt,'%m-%d-%y')), col11 = CONCAT('End_date', '.',date_format(OLD.stat_e_dt,'%m-%d-%y')), col14 = CONCAT('Rate', '.',(select assist_rate from mkt_assist where idmkt_assist = OLD.idmkt_assist)), col15 = CONCAT('Days', '.',OLD.no_days), col13 = CONCAT('Summary', '.',(select summary from mkt_assist where idmkt_assist = OLD.idmkt_assist)); INSERT INTO log_global set last_update = now(), updater = (select concat(firstname,' ',lastname) as name from emp where emp_id=(case when (NEW.updated_by = 0) then 1 else NEW.updated_by end)), table_update = 'mkt_assist_status', actions = 'N', table_id = CONCAT('idmkt_assist_status', '.',NEW.idmkt_assist_status, '\n','idmkt_assist', '.',NEW.idmkt_assist), col1 = pname, col2 = CONCAT('Ref_table', '.',(select assist_ref_table from mkt_assist where idmkt_assist = NEW.idmkt_assist)), col4 = CONCAT('Status', '.',(select look_values from look_values where look_values_id=NEW.assist_status)), col5 = CONCAT('Company', '.',(select companyname from mkt_company where idmkt_company = (select compname from mkt_open where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = NEW.idmkt_assist)))), col7 = CONCAT('Client', '.',(select companyname from mkt_company where idmkt_company= (select client_name from mkt_open where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = NEW.idmkt_assist)))), col8 = CONCAT('Location', '.',(select concat(look_city .city_name,',',look_state .state_name) from look_city inner join look_state on look_city.state_id=look_state.state_id where city_id= (select location from mkt_open where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = NEW.idmkt_assist)))), col10 = CONCAT('Start_date', '.',date_format(NEW.stat_st_dt,'%m-%d-%y')), col11 = CONCAT('End_date', '.',date_format(NEW.stat_e_dt,'%m-%d-%y')), col14 = CONCAT('Rate', '.',(select assist_rate from mkt_assist where idmkt_assist = NEW.idmkt_assist)), col15 = CONCAT('Days', '.',NEW.no_days), col13 = CONCAT('Summary', '.',(select summary from mkt_assist where idmkt_assist = NEW.idmkt_assist)); END IF; END CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `mkt_assist_status_after_delete` AFTER DELETE ON `mkt_assist_status` FOR EACH ROW begin DECLARE vUser varchar(50); DECLARE pname varchar(100); IF(OLD.updated_by='0')THEN set vUser='Admin'; else set vUser=(select concat(firstname, ' ',lastname) from emp where emp_id = OLD.updated_by); END IF; IF((select assist_ref_table from mkt_assist where idmkt_assist = OLD.idmkt_assist)='Employee')THEN set pname =(select concat(firstname, ' ',lastname) from emp where emp_id = (select assist_ref_id from mkt_assist where idmkt_assist = OLD.idmkt_assist)); else set pname = (select concat(prosp_first_name, ' ',prosp_last_name) from prosp_consult where prosp_consult_id = (select assist_ref_id from mkt_assist where idmkt_assist = OLD.idmkt_assist)); END IF; INSERT INTO log_global set last_update = now(), updater = vUser, table_update = 'mkt_assist_status', actions = 'D', table_id = CONCAT('idmkt_assist_status', '.',OLD.idmkt_assist_status, '\n','idmkt_assist', '.',OLD.idmkt_assist), col1 = pname, col2 = CONCAT('Ref_table', '.',(select assist_ref_table from mkt_assist where idmkt_assist = OLD.idmkt_assist)), col4 = CONCAT('Status', '.',(select look_values from look_values where look_values_id=OLD.assist_status)), col5 = CONCAT('Company', '.',(select companyname from mkt_company where idmkt_company = (select compname from mkt_open where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = OLD.idmkt_assist)))), col7 = CONCAT('Client', '.',(select companyname from mkt_company where idmkt_company= (select client_name from mkt_open where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = OLD.idmkt_assist)))), col8 = CONCAT('Location', '.',(select concat(look_city .city_name,',',look_state .state_name) from look_city inner join look_state on look_city.state_id=look_state.state_id where city_id= (select location from mkt_open where idmkt_open=(select idmkt_open from mkt_assist where idmkt_assist = OLD.idmkt_assist)))), col10 = CONCAT('Start_date', '.',date_format(OLD.stat_st_dt,'%m-%d-%y')), col11 = CONCAT('End_date', '.',date_format(OLD.stat_e_dt,'%m-%d-%y')), col14 = CONCAT('Rate', '.',(select assist_rate from mkt_assist where idmkt_assist = OLD.idmkt_assist)), col15 = CONCAT('Days', '.',OLD.no_days), col13 = CONCAT('Summary', '.',(select summary from mkt_assist where idmkt_assist = OLD.idmkt_assist)); END