CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `dev_est_reqs_case_after_insert` AFTER INSERT ON `dev_est_reqs_case` 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     	= 'dev_est_reqs_case',
actions			= 'A',
table_id		=CONCAT('iddev_est_reqs_case', '.',NEW.iddev_est_reqs_case),	
col1 =	CONCAT('iddev_est_reqs', '.',NEW.iddev_est_reqs),
col2 =	CONCAT('created_by', '.',NEW.created_by),
col3 =	CONCAT('disp_order', '.',NEW.disp_order),
col4 =	CONCAT('reqs_cases', '.',NEW.reqs_cases),
col5 =	CONCAT('case_data', '.',NEW.case_data),
col6 =	CONCAT('case_result_desc', '.',NEW.case_result_desc),
col7 =	CONCAT('case_status', '.',(select look_values from look_values where look_values_id = NEW.case_status)),
col9   = CONCAT('execute_dt', '.',date_format(NEW.execute_dt,'%m-%d-%y')),	
col10   = CONCAT('create_dt', '.',date_format(NEW.create_dt,'%m-%d-%y')),
col11   = CONCAT('create_end_dt', '.',date_format(NEW.create_end_dt,'%m-%d-%y')),
col12 =	CONCAT('executed_by', '.',(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.executed_by));
	
		

 


END







CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `dev_est_reqs_case_after_update` AFTER UPDATE ON `dev_est_reqs_case` 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     	= 'dev_est_reqs_case',
actions			= 'O',
table_id		=CONCAT('iddev_est_reqs_case', '.',OLD.iddev_est_reqs_case),	
col1 =	CONCAT('iddev_est_reqs', '.',OLD.iddev_est_reqs),
col2 =	CONCAT('created_by', '.',OLD.created_by),
col3 =	CONCAT('disp_order', '.',OLD.disp_order),
col4 =	CONCAT('reqs_cases', '.',OLD.reqs_cases),
col5 =	CONCAT('case_data', '.',OLD.case_data),
col6 =	CONCAT('case_result_desc', '.',OLD.case_result_desc),
col7 =	CONCAT('case_status', '.',(select look_values from look_values where look_values_id = OLD.case_status)),
col9   = CONCAT('execute_dt', '.',date_format(OLD.execute_dt,'%m-%d-%y')),	
col10   = CONCAT('create_dt', '.',date_format(OLD.create_dt,'%m-%d-%y')),
col11   = CONCAT('create_end_dt', '.',date_format(OLD.create_end_dt,'%m-%d-%y')),
col12 =	CONCAT('executed_by', '.',(select concat(firstname, ' ',lastname) from emp where emp_id = OLD.executed_by));	
	
	
	

INSERT INTO log_global
set
last_update 	= now(),
updater         = vUser,
table_update    = 'dev_est_reqs_case',
actions			= 'N',
table_id		=	CONCAT('iddev_est_reqs_case', '.',NEW.iddev_est_reqs_case),	
col1 			=	CONCAT('iddev_est_reqs', '.',NEW.iddev_est_reqs),
col2			=	CONCAT('created_by', '.',NEW.created_by),
col3			=	CONCAT('disp_order', '.',NEW.disp_order),
col4 			=	CONCAT('reqs_cases', '.',NEW.reqs_cases),
col5 			=	CONCAT('case_data', '.',NEW.case_data),
col6 			=	CONCAT('case_result_desc', '.',NEW.case_result_desc),
col7 			=	CONCAT('case_status', '.',(select look_values from look_values where look_values_id = NEW.case_status)),
col9   			= CONCAT('execute_dt', '.',date_format(NEW.execute_dt,'%m-%d-%y')),	
col10   		= CONCAT('create_dt', '.',date_format(NEW.create_dt,'%m-%d-%y')),
col11   		= CONCAT('create_end_dt', '.',date_format(NEW.create_end_dt,'%m-%d-%y')),
col12 			=	CONCAT('executed_by', '.',(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.executed_by));	
	



END