CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `mkt_company_after_insert` AFTER INSERT ON `mkt_company` FOR EACH ROW begin


DECLARE vUser varchar(50);
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;

INSERT INTO log_global
set
last_update = now(),
updater = vUser,
table_update = 'mkt_company',
actions = 'A',
table_id = CONCAT('mkt_company_id', '.',NEW.idmkt_company),
col1 = CONCAT('companyname', '.',NEW.companyname),
col4 = CONCAT('company_info', '.',NEW.company_info),
col7 = CONCAT('comp_website', '.',NEW.comp_website),
col10 = CONCAT('comp_phone', '.',NEW.comp_phone),
col11 = CONCAT('comp_email', '.',NEW.comp_email),
col12 = CONCAT('req_type', '.',(select look_values from look_values where 
look_values_id = NEW.req_type)),
col13 = CONCAT('track', '.',(select look_values from look_values 
where look_values_id = NEW.track)),
col14 = CONCAT('block', '.',(select look_values from look_values 
where look_values_id = NEW.block)),
col15 = CONCAT('block_dt', 
'.',date_format(NEW.block_dt,'%m-%d-%y'));
END







CREATE DEFINER=`jitenpatel`@`%` TRIGGER `mkt_company_after_update` AFTER UPDATE ON `mkt_company` FOR EACH ROW begin

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_company',
actions = 'O',
table_id = CONCAT('mkt_company_id', '.',OLD.idmkt_company),
col1 = CONCAT('companyname', '.',OLD.companyname),
col4 = CONCAT('company_info', '.',OLD.company_info),
col7 = CONCAT('comp_website', '.',OLD.comp_website),
col10 = CONCAT('comp_phone', '.',OLD.comp_phone),
col11 = CONCAT('comp_email', '.',OLD.comp_email),
col12 = CONCAT('req_type', '.',(select look_values from look_values where 
look_values_id = OLD.req_type)),
col13 =CONCAT('track', '.',(select look_values from look_values 
where look_values_id = OLD.track)),
col14 = CONCAT('block', '.',(select look_values from look_values 
where look_values_id = OLD.block)),
col15 = CONCAT('block_dt', '.',OLD.block_dt);

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_company',
actions = 'N',
table_id = CONCAT('mkt_company_id', '.',NEW.idmkt_company),
col1 = CONCAT('companyname', '.',NEW.companyname),
col4 = CONCAT('company_info', '.',NEW.company_info),
col7 = CONCAT('comp_website', '.',NEW.comp_website),
col10 = CONCAT('comp_phone', '.',NEW.comp_phone),
col11 = CONCAT('comp_email', '.',NEW.comp_email),
col12 = CONCAT('req_type', '.',(select look_values from look_values where 
look_values_id = NEW.req_type)),
col13 =CONCAT('track', '.',(select look_values from look_values 
where look_values_id = NEW.track)) ,
col14 = CONCAT('block', '.',(select look_values from look_values 
where look_values_id = NEW.block)),
col15 = CONCAT('block_dt', '.',NEW.block_dt);

END








CREATE DEFINER=`jitenpatel`@`%` TRIGGER `mkt_company_after_delete` AFTER DELETE ON `mkt_company` FOR EACH ROW begin

DECLARE vUser varchar(50);
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;

INSERT INTO log_global
set
last_update = now(),
updater = vUser,
table_update = 'mkt_company',
actions = 'D',
table_id = CONCAT('mkt_company_id', '.',OLD.idmkt_company),
col1 = CONCAT('companyname', '.',OLD.companyname),
col4 = CONCAT('company_info', '.',OLD.company_info),
col7 = CONCAT('comp_website', '.',OLD.comp_website),
col10 = CONCAT('comp_phone', '.',OLD.comp_phone),
col11 = CONCAT('comp_email', '.',OLD.comp_email),
col12 = CONCAT('req_type', '.',(select look_values from look_values where 
look_values_id = OLD.req_type)),
col13 = CONCAT('track', '.',(select look_values from look_values 
where look_values_id = OLD.track)),
col14 = CONCAT('block', '.',(select look_values from look_values 
where look_values_id = OLD.block)),
col15 = CONCAT('block_dt', '.',OLD.block_dt);


END