CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `emp_org_after_insert` AFTER INSERT ON `emp_org` FOR EACH ROW begin
 
DECLARE vUser varchar(50);
IF(NEW.updater='0')THEN
set vUser='Admin';
else
set vUser=(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.updater);
END IF;

INSERT INTO log_global 
set
last_update 	        = now(),
updater                 = vUser,
table_update     	= 'emp_org',
actions		        = 'A',	
table_id		= CONCAT('emp_org_id', '.',NEW.emp_org_id),
col1			= CONCAT('comp_name', '.',(SELECT `companyname` FROM `company` WHERE `comp_id`=NEW.comp_id)),
col4			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=NEW.emp_id)),
col7           	= CONCAT('org_start_dt', '.',date_format(NEW.org_start_dt,'%m-%d-%y')),
col10           	= CONCAT('org_end_dt', '.',date_format(NEW.org_end_dt,'%m-%d-%y'));


END







CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `emp_org_after_update` AFTER UPDATE ON `emp_org` 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.updater = 0) then 1 else OLD.updater end)),
table_update     	= 'emp_org',
actions		        = 'O',	
table_id		= CONCAT('emp_org_id', '.',OLD.emp_org_id),
col1			= CONCAT('comp_name', '.',(SELECT `companyname` FROM `company` WHERE `comp_id`=OLD.comp_id)),
col4			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=OLD.emp_id)),
col7            	= CONCAT('org_start_dt', '.',date_format(OLD.org_start_dt,'%m-%d-%y')),
col10            	= CONCAT('org_end_dt', '.',date_format(OLD.org_end_dt,'%m-%d-%y'));



INSERT INTO log_global 
set
last_update 	        = now(),
updater                 = (select concat(firstname,' ',lastname) as name from emp where emp_id=(case when (NEW.updater = 0) then 1 else NEW.updater end)),
table_update     	= 'emp_org',
actions		        = 'N',	
table_id		= CONCAT('emp_org_id', '.',NEW.emp_org_id),
col1			= CONCAT('comp_name', '.',(SELECT `companyname` FROM `company` WHERE `comp_id`=NEW.comp_id)),
col4			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=NEW.emp_id)),
col7           	= CONCAT('org_start_dt', '.',date_format(NEW.org_start_dt,'%m-%d-%y')),
col10            	= CONCAT('org_end_dt', '.',date_format(NEW.org_end_dt,'%m-%d-%y'));


END







CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `emp_org_after_delete` AFTER DELETE ON `emp_org` FOR EACH ROW begin
 
DECLARE vUser varchar(50);
IF(OLD.updater='0')THEN
set vUser='Admin';
else
set vUser=(select concat(firstname, ' ',lastname) from emp where emp_id = OLD.updater);
END IF; 

INSERT INTO log_global 
set
last_update 	        = now(),
updater                 = vUser,
table_update     	= 'emp_org',
actions		        = 'D',	
table_id		= CONCAT('emp_org_id', '.',OLD.emp_org_id),
col1			= CONCAT('comp_name', '.',(SELECT `companyname` FROM `company` WHERE `comp_id`=OLD.comp_id)),
col4			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=OLD.emp_id)),
col7            	= CONCAT('org_start_dt', '.',date_format(OLD.org_start_dt,'%m-%d-%y')),
col10            	= CONCAT('org_end_dt', '.',date_format(OLD.org_end_dt,'%m-%d-%y'));


END