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