CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `emp_address_after_insert` AFTER INSERT ON `emp_address` FOR EACH ROW begin
 
DECLARE vUser varchar(50);
DECLARE my_zip_code varchar(45);
IF(NEW.updater='0')THEN
set vUser='Admin';
else
set vUser=(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.updater);
END IF;
set my_zip_code=(select zip_code from look_postal where postal_id=(select postal_id from look_street where look_street_id=(SELECT look_street_id from look_street_no where `look_street_no_id`=NEW.look_street_no_id)));
 
INSERT INTO log_global 
set
last_update 	        = now(),
updater                 = vUser,
table_update     	= 'emp_address',
actions			= 'A',	
table_id		= CONCAT('emp_add_id', '.',NEW.emp_add_id),
col1			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=NEW.emp_id)),
col2            	= CONCAT('house_apt_no', '.',NEW.house_apt_no),

col4			= CONCAT('from_dt', '.',date_format(NEW.from_dt,'%m-%d-%y')),
col5			= CONCAT('to_dt', '.',date_format(NEW.to_dt,'%m-%d-%y')),

col7			= CONCAT('add_type', '.',NEW.add_type),
col8            = CONCAT('Street_No','.',(select look_street_no from look_street_no where look_street_no_id=NEW.look_street_no_id)),
col9            = CONCAT('Street_Name','.',(select street_name from look_street where look_street_id=(select look_street_id from look_street where look_street_id=(SELECT look_street_id from look_street_no where `look_street_no_id`=NEW.look_street_no_id)))),

col10            = CONCAT('Street_Type','.',(select look_values from look_values where 	look_values_id=
(select street_type from look_street where look_street_id=(SELECT look_street_id from look_street_no where `look_street_no_id`=NEW.look_street_no_id)))),
col11            = CONCAT('City_Name','.',(select city_name from look_city where city_id=(select city_id from look_postal where zip_code=my_zip_code))),
col13            = CONCAT('State_Name','.',(select state_name from look_state where `state_id`=(select state_id from look_city where city_id=(select city_id from look_postal where postal_id=(select postal_id from look_street where look_street_id=(select look_street_id from look_street_no where look_street_no_id=NEW.look_street_no_id)))))),
col14            = CONCAT('Postal_Code','.',my_zip_code);


END









CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `emp_address_after_update` AFTER UPDATE ON `emp_address` FOR EACH ROW begin
 
DECLARE old_zip_code varchar(45); 
DECLARE new_zip_code varchar(45);

set old_zip_code=(select zip_code from look_postal where postal_id=(select postal_id from look_street where look_street_id=(SELECT look_street_id from look_street_no where `look_street_no_id`=OLD.look_street_no_id))); 

set new_zip_code=(select zip_code from look_postal where postal_id=(select postal_id from look_street where look_street_id=(SELECT look_street_id from look_street_no where `look_street_no_id`=NEW.look_street_no_id))); 
 
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_address',
actions			= 'O',	
table_id		= CONCAT('emp_add_id', '.',OLD.emp_add_id),
col1			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=OLD.emp_id)),
col2            	= CONCAT('house_apt_no', '.',OLD.house_apt_no),
col4			= CONCAT('from_dt', '.',date_format(OLD.from_dt,'%m-%d-%y')),
col5			= CONCAT('to_dt', '.',date_format(OLD.to_dt,'%m-%d-%y')),
col7			= CONCAT('add_type', '.',OLD.add_type),
col8            = CONCAT('Street_No','.',(select look_street_no from look_street_no where look_street_no_id=OLD.look_street_no_id)),
col9            = CONCAT('Street_Name','.',(select street_name from look_street where look_street_id=(select look_street_id from look_street where look_street_id=(SELECT look_street_id from look_street_no where `look_street_no_id`=OLD.look_street_no_id)))),
col10            = CONCAT('Street_Type','.',(select look_values from look_values where 	look_values_id=
(select street_type from look_street where look_street_id=(SELECT look_street_id from look_street_no where `look_street_no_id`=OLD.look_street_no_id)))),
col11            = CONCAT('City_Name','.',(select city_name from look_city where city_id=(select city_id from look_postal where zip_code=old_zip_code))),
col13            = CONCAT('State_Name','.',(select state_name from look_state where `state_id`=(select state_id from look_city where city_id=(select city_id from look_postal where postal_id=(select postal_id from look_street where look_street_id=(select look_street_id from look_street_no where look_street_no_id=OLD.look_street_no_id)))))),
col14            = CONCAT('Postal_Code','.',old_zip_code);


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_address',
actions			= 'N',	
table_id		= CONCAT('emp_add_id', '.',NEW.emp_add_id),
col1			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=NEW.emp_id)),
col2            	= CONCAT('house_apt_no', '.',NEW.house_apt_no),
col4			= CONCAT('from_dt', '.',date_format(NEW.from_dt,'%m-%d-%y')),
col5			= CONCAT('to_dt', '.',date_format(NEW.to_dt,'%m-%d-%y')),
col7			= CONCAT('add_type', '.',NEW.add_type),
col8            = CONCAT('Street_No','.',(select look_street_no from look_street_no where look_street_no_id=NEW.look_street_no_id)),
col9            = CONCAT('Street_Name','.',(select street_name from look_street where look_street_id=(select look_street_id from look_street where look_street_id=(SELECT look_street_id from look_street_no where `look_street_no_id`=NEW.look_street_no_id)))),
col10            = CONCAT('Street_Type','.',(select look_values from look_values where 	look_values_id=
(select street_type from look_street where look_street_id=(SELECT look_street_id from look_street_no where `look_street_no_id`=NEW.look_street_no_id)))),
col11            = CONCAT('City_Name','.',(select city_name from look_city where city_id=(select city_id from look_postal where zip_code=new_zip_code))),
col13            = CONCAT('State_Name','.',(select state_name from look_state where `state_id`=(select state_id from look_city where city_id=(select city_id from look_postal where postal_id=(select postal_id from look_street where look_street_id=(select look_street_id from look_street_no where look_street_no_id=NEW.look_street_no_id)))))),
col14            = CONCAT('Postal_Code','.',new_zip_code);


END









CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `emp_address_after_delete` AFTER DELETE ON `emp_address` FOR EACH ROW begin
 
DECLARE vUser varchar(50);
DECLARE my_zip_code varchar(45);
IF(OLD.updater='0')THEN
set vUser='Admin';
else
set vUser=(select concat(firstname, ' ',lastname) from emp where emp_id = OLD.updater);
END IF; 
set my_zip_code=(select zip_code from look_postal where postal_id=(select postal_id from look_street where look_street_id=(SELECT look_street_id from look_street_no where `look_street_no_id`=OLD.look_street_no_id)));
 
INSERT INTO log_global 
set
last_update 	        = now(),
updater                 = vUser,
table_update     	= 'emp_address',
actions		        = 'D',	
table_id		= CONCAT('emp_add_id', '.',OLD.emp_add_id),
col1			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=OLD.emp_id)),
col2            	= CONCAT('house_apt_no', '.',OLD.house_apt_no),
col4			= CONCAT('from_dt', '.',date_format(OLD.from_dt,'%m-%d-%y')),
col5			= CONCAT('to_dt', '.',date_format(OLD.to_dt,'%m-%d-%y')),
col7			= CONCAT('add_type', '.',OLD.add_type),
col8            = CONCAT('Street_No','.',(select look_street_no from look_street_no where look_street_no_id=OLD.look_street_no_id)),
col9            = CONCAT('Street_Name','.',(select street_name from look_street where look_street_id=(select look_street_id from look_street where look_street_id=(SELECT look_street_id from look_street_no where `look_street_no_id`=OLD.look_street_no_id)))),
col10            = CONCAT('Street_Type','.',(select look_values from look_values where 	look_values_id=
(select street_type from look_street where look_street_id=(SELECT look_street_id from look_street_no where `look_street_no_id`=OLD.look_street_no_id)))),
col11            = CONCAT('City_Name','.',(select city_name from look_city where city_id=(select city_id from look_postal where zip_code=my_zip_code))),
col13            = CONCAT('State_Name','.',(select state_name from look_state where `state_id`=(select state_id from look_city where city_id=(select city_id from look_postal where postal_id=(select postal_id from look_street where look_street_id=(select look_street_id from look_street_no where look_street_no_id=OLD.look_street_no_id)))))),
col14            = CONCAT('Postal_Code','.',my_zip_code);


END