CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `mkt_open_after_insert` AFTER INSERT ON `mkt_open` FOR EACH ROW begin DECLARE vUser varchar(50); DECLARE com_name varchar(100); DECLARE cli_Name varchar(100); DECLARE loc varchar(100); DECLARE track varchar(100); DECLARE nreq_type 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; set loc =(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=NEW.location); set com_name =(select companyname from mkt_company where idmkt_company=NEW.compname); set cli_Name =(select companyname from mkt_company where idmkt_company=NEW.client_name); set nreq_type =(select look_values from look_values where look_values_id = NEW.req_type); set track =(select look_values from look_values where look_values_id = NEW.track); INSERT INTO mkt_open_log set last_updated = now(), updated_by = vUser, trans_type = 'A', idmkt_open = NEW.idmkt_open, compname = com_name , client_name = cli_Name , location = loc, contact_info = NEW.contact_info, open_details = NEW.open_details, open_dt = NEW.open_dt, close_dt = NEW.close_dt, req_type = nreq_type, track = track, summary = NEW.summary; END CREATE DEFINER=`jitenpatel`@`%` TRIGGER `mkt_open_after_update` AFTER UPDATE ON `mkt_open` FOR EACH ROW begin DECLARE vUser varchar(50); DECLARE com_name varchar(100); DECLARE cli_Name varchar(100); DECLARE ncom_name varchar(100); DECLARE ncli_Name varchar(100); DECLARE loc varchar(100); DECLARE nloc varchar(100); DECLARE nreq_type varchar(100); DECLARE req_type varchar(100); DECLARE ntrack varchar(100); DECLARE track varchar(100); IF(NEW.updated_by='0')THEN set vUser='Admin'; else set vUser=(select concat(firstname, ' ',lastname) from emp where emp_id = OLD.updated_by); END IF; set loc =(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=OLD.location); set nloc =(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=NEW.location); set com_name =(select companyname from mkt_company where idmkt_company=OLD.compname); set cli_Name =(select companyname from mkt_company where idmkt_company=OLD.client_name); set ncom_name =(select companyname from mkt_company where idmkt_company=NEW.compname); set ncli_Name =(select companyname from mkt_company where idmkt_company=NEW.client_name); set nreq_type =(select look_values from look_values where look_values_id = NEW.req_type); set req_type =(select look_values from look_values where look_values_id = OLD.req_type); set ntrack =(select look_values from look_values where look_values_id = NEW.track); set track =(select look_values from look_values where look_values_id = OLD.track); INSERT INTO mkt_open_log set last_updated = now(), updated_by = vUser, trans_type = 'O', idmkt_open = OLD.idmkt_open, compname = com_name , client_name = cli_Name , location = loc, contact_info = OLD.contact_info, open_details = OLD.open_details, open_dt = OLD.open_dt, close_dt = OLD.close_dt, req_type = req_type, track = track, summary = OLD.summary; INSERT INTO mkt_open_log set last_updated = now(), updated_by = vUser, trans_type = 'N', idmkt_open = NEW.idmkt_open, compname = ncom_name, client_name = ncli_Name, location = nloc, contact_info = NEW.contact_info, open_details = NEW.open_details, open_dt = NEW.open_dt, close_dt = NEW.close_dt, req_type = nreq_type, track = ntrack, summary = NEW.summary; END CREATE DEFINER=`jitenpatel`@`%` TRIGGER `mkt_open_after_delete` AFTER DELETE ON `mkt_open` FOR EACH ROW begin DECLARE vUser varchar(50); DECLARE com_name varchar(100); DECLARE cli_Name varchar(100); DECLARE loc varchar(100); DECLARE nreq_type varchar(100); DECLARE ntrack 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; set loc =(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=OLD.location); set com_name =(select companyname from mkt_company where idmkt_company=OLD.compname); set cli_Name =(select companyname from mkt_company where idmkt_company=OLD.client_name); set nreq_type =(select look_values from look_values where look_values_id = OLD.req_type); set ntrack =(select look_values from look_values where look_values_id = OLD.track); INSERT INTO mkt_open_log set last_updated = now(), updated_by = vUser, trans_type = 'D', idmkt_open = OLD.idmkt_open, compname = com_name , client_name = cli_Name , location = loc, contact_info = OLD.contact_info, open_details = OLD.open_details, open_dt = OLD.open_dt, close_dt = OLD.close_dt, req_type = nreq_type, track = ntrack, summary = OLD.summary; END