CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `dev_est_reqs_case_result_after_insert` AFTER INSERT ON `dev_est_reqs_case_result` 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_result',
actions			=	'A',
table_id		=	CONCAT('iddev_est_reqs_case_result', '.',NEW.iddev_est_reqs_case_result),	
col1			=	CONCAT('iddev_est_reqs_case', '.',NEW.iddev_est_reqs_case),
col2 			=	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)),
col12 			=	CONCAT('executed_by', '.',(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.executed_by)),
col9   			= CONCAT('execute_dt', '.',date_format(NEW.execute_dt,'%m-%d-%y'));
	
		

 


END









CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `dev_est_reqs_case_result_after_update` AFTER UPDATE ON `dev_est_reqs_case_result` 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_result',
actions			=	'O',
table_id		=	CONCAT('iddev_est_reqs_case_result', '.',OLD.iddev_est_reqs_case_result),	
col1			=	CONCAT('iddev_est_reqs_case', '.',OLD.iddev_est_reqs_case),
col2 			=	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)),
col12 			=	CONCAT('executed_by', '.',(select concat(firstname, ' ',lastname) from emp where emp_id = OLD.executed_by)),
col9   			= CONCAT('execute_dt', '.',date_format(OLD.execute_dt,'%m-%d-%y'));
	

 
INSERT INTO log_global
set
last_update 	        = now(),
updater                 = vUser,
table_update     	= 'dev_est_reqs_case_result',
actions			=	'N',
table_id		=	CONCAT('iddev_est_reqs_case_result', '.',NEW.iddev_est_reqs_case_result),	
col1			=	CONCAT('iddev_est_reqs_case', '.',NEW.iddev_est_reqs_case),
col2 			=	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)),
col12 			=	CONCAT('executed_by', '.',(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.executed_by)),
col9   			= CONCAT('execute_dt', '.',date_format(NEW.execute_dt,'%m-%d-%y'));
	
		

 


END










CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `dev_est_reqs_case_result_after_delete` AFTER DELETE ON `dev_est_reqs_case_result` 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_result',
actions			=	'D',
table_id		=	CONCAT('iddev_est_reqs_case_result', '.',OLD.iddev_est_reqs_case_result),	
col1			=	CONCAT('iddev_est_reqs_case', '.',OLD.iddev_est_reqs_case),
col2 			=	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)),
col12 			=	CONCAT('executed_by', '.',(select concat(firstname, ' ',lastname) from emp where emp_id = OLD.executed_by)),
col9   			= CONCAT('execute_dt', '.',date_format(OLD.execute_dt,'%m-%d-%y'));
	


END