CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `all_users_after_insert` AFTER INSERT ON `all_users` FOR EACH ROW begin

DECLARE vUser varchar(50);
IF(NEW.emp_id='0')THEN
set vUser='Admin';
else
set vUser=(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.emp_id);
END IF;
 
INSERT INTO log_global 
set
last_update 	        = now(),
updater                 = vUser,
table_update            = 'all_users',
actions			= 'A',	
table_id		= CONCAT('uid_id', '.',NEW.uid_id),
col1			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=NEW.emp_id)),
col2			= CONCAT('user_name', '.',NEW.user_name),
col3            	= CONCAT('password', '.',NEW.password),
col4			= CONCAT('status', '.',(SELECT (case when(status=1) then 'active' when (status=0) then 'inactive' when (status=2) then 'locked' end) as ststus FROM `all_users`  where status=NEW.status limit 1));
END








CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `all_users_after_update` AFTER UPDATE ON `all_users` 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.emp_id = 0) then 1 else OLD.emp_id end)),
table_update            = 'all_users',
actions			= 'O',	
table_id		= CONCAT('uid_id', '.',OLD.uid_id),
col1			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=OLD.emp_id)),
col2			= CONCAT('user_name', '.',OLD.user_name),
col3            	= CONCAT('password', '.',OLD.password),
col4			= CONCAT('status', '.',(SELECT (case when(status=1) then 'active' when (status=0) then 'inactive' when (status=2) then 'locked' end) as ststus FROM `all_users`  where status=OLD.status limit 1));

INSERT INTO log_global 
set
last_update 	        = now(),
updater                 = (select concat(firstname,' ',lastname) as name from emp where emp_id=(case when (NEW.emp_id = 0) then 1 else NEW.emp_id end)),
table_update            = 'all_users',
actions			= 'N',	
table_id		= CONCAT('uid_id', '.',NEW.uid_id),
col1			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=NEW.emp_id)),
col2			= CONCAT('user_name', '.',NEW.user_name),
col3            	= CONCAT('password', '.',NEW.password),
col4			= CONCAT('status', '.',(SELECT (case when(status=1) then 'active' when (status=0) then 'inactive' when (status=2) then 'locked' end) as ststus FROM `all_users`  where status=NEW.status limit 1));

END








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

INSERT INTO log_global 
set
last_update 	        = now(),
updater                 = vUser,
table_update            = 'all_users',
actions			= 'D',	
table_id		= CONCAT('uid_id', '.',OLD.uid_id),
col1			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=OLD.emp_id)),
col2			= CONCAT('user_name', '.',OLD.user_name),
col3            	= CONCAT('password', '.',OLD.password),
col4			= CONCAT('status', '.',(SELECT (case when(status=1) then 'active' when (status=0) then 'inactive' when (status=2) then 'locked' end) as ststus FROM `all_users`  where status=OLD.status limit 1));

END