CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `engage` AFTER INSERT ON `engage` 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            = 'engage',
actions			= 'A',	
table_id		= CONCAT('engage_id ', '.',NEW.engage_id ),
col1            = CONCAT('Client_name', '.',(select companyname from company  where comp_id=NEW.srv_client)),
col2            = CONCAT('Vendor_name', '.',(SELECT companyname FROM company WHERE comp_id = (SELECT comp_id FROM engage_comp
					WHERE (engage_id =NEW.engage_id) LIMIT 1 ))),
col4			= CONCAT('Frequency', '.',(select look_values from look_values where look_values_id=NEW.sr_frequency)),
col5			= CONCAT('Paytype', '.',(select look_values from look_values where look_values_id=NEW.srv_paytype)),
col10                    = CONCAT('start_date', '.',date_format(NEW.	srv_st_dt,'%m-%d-%y')),
col11                   = CONCAT('end_date', '.',date_format(NEW.srv_e_dt,'%m-%d-%y')),
col7            	= CONCAT('Amount', '.',NEW.srv_amt),
col13                    = CONCAT('Summary', '.',NEW.eg_sumry);
END







CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `engage_after_update` AFTER UPDATE ON `engage` 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            = 'engage',
actions			= 'O',	
table_id		= CONCAT('engage_id ', '.',OLD.engage_id ),
col1            = CONCAT('Client_name', '.',(select companyname from company  where comp_id=OLD.srv_client)),
col2            = CONCAT('Vendor_name', '.',(SELECT companyname FROM company WHERE comp_id = (SELECT comp_id FROM engage_comp
					WHERE (engage_id =OLD.engage_id) LIMIT 1 ))),
col4			= CONCAT('Frequency', '.',(select look_values from look_values where look_values_id=OLD.sr_frequency)),
col5			= CONCAT('Paytype', '.',(select look_values from look_values where look_values_id=OLD.srv_paytype)),
col10                    = CONCAT('start_date', '.',date_format(OLD.srv_st_dt,'%m-%d-%y')),
col11                   = CONCAT('end_date', '.',date_format(OLD.srv_e_dt,'%m-%d-%y')),
col7            	= CONCAT('Amount', '.',OLD.srv_amt),
col13                    = CONCAT('Summary', '.',OLD.eg_sumry);
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            = 'engage',
actions			= 'N',	
table_id		= CONCAT('engage_id ', '.',NEW.engage_id ),
col1            = CONCAT('Client_name', '.',(select companyname from company  where comp_id=NEW.srv_client)),
col2            = CONCAT('Vendor_name', '.',(SELECT companyname FROM company WHERE comp_id = (SELECT comp_id FROM engage_comp
					WHERE (engage_id =NEW.engage_id) LIMIT 1 ))),
col4			= CONCAT('Frequency', '.',(select look_values from look_values where look_values_id=NEW.sr_frequency)),
col5			= CONCAT('Paytype', '.',(select look_values from look_values where look_values_id=NEW.srv_paytype)),
col10                    = CONCAT('start_date', '.',date_format(NEW.	srv_st_dt,'%m-%d-%y')),
col11                   = CONCAT('end_date', '.',date_format(NEW.srv_e_dt,'%m-%d-%y')),
col7            	= CONCAT('Amount', '.',NEW.srv_amt),
col13                    = CONCAT('Summary', '.',NEW.eg_sumry);


END







CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `engage_after_delete` AFTER DELETE ON `engage` 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            = 'engage',
actions			= 'D',	
table_id		= CONCAT('engage_id ', '.',OLD.engage_id ),
col1            = CONCAT('Client_name', '.',(select companyname from company  where comp_id=OLD.srv_client)),
col2            = CONCAT('Vendor_name', '.',(SELECT companyname FROM company WHERE comp_id = (SELECT comp_id FROM engage_comp
					WHERE (engage_id =OLD.engage_id) LIMIT 1 ))),
col4			= CONCAT('Frequency', '.',(select look_values from look_values where look_values_id=OLD.sr_frequency)),
col5			= CONCAT('Paytype', '.',(select look_values from look_values where look_values_id=OLD.srv_paytype)),
col10                    = CONCAT('start_date', '.',date_format(OLD.srv_st_dt,'%m-%d-%y')),
col11                   = CONCAT('end_date', '.',date_format(OLD.srv_e_dt,'%m-%d-%y')),
col7            	= CONCAT('Amount', '.',OLD.srv_amt),
col13                    = CONCAT('Summary', '.',OLD.eg_sumry);


END