CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `prosp_corp_after_insert` AFTER INSERT ON `prosp_corp` 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 (NEW.updated_by IS NULL) then 'Admin' else NEW.updated_by end)),
table_update           = 'prosp_corp',
actions		           = 'A',	
table_id	           = CONCAT('idprosp_corp', '.',NEW.idprosp_corp),
col1                   = CONCAT('prosp_corp_type', '.',(select look_values from look_values where look_values_id=NEW.prosp_corp_type)),
col2                   = CONCAT('corp_entity_name', '.',NEW.corp_entity_name),
col4                   = CONCAT('corp_source', '.',(select look_values from look_values where look_values_id=NEW.corp_source)),
col5                   = CONCAT('corp_contacts','.',NEW.corp_contacts),
col6                   = CONCAT('corp_address','.',NEW.corp_address),
col7                   = CONCAT('corp_level','.',(select look_values from look_values where look_values_id=NEW.corp_level)),
col8                   = CONCAT('register_yr','.',NEW.register_yr),
col10                  = CONCAT('register_state','.',NEW.register_state),
col11                  = CONCAT('acc_mgr', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id= NEW.acc_mgr)),
col13                  = CONCAT('topic_discussed','.',NEW.topic_discussed),
col14                  = CONCAT('about_corp','.',NEW.about_corp);

  

END







CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `prosp_corp_after_update` AFTER UPDATE ON `prosp_corp` 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.updated_by = 0) then 1 else OLD.updated_by end)),
table_update           = 'prosp_corp',
actions		           = 'O',	
table_id	           = CONCAT('idprosp_corp', '.',OLD.idprosp_corp),
col1                   = CONCAT('prosp_corp_type', '.',(select look_values from look_values where look_values_id=OLD.prosp_corp_type)),
col2                   = CONCAT('corp_entity_name', '.',OLD.corp_entity_name),
col4                   = CONCAT('corp_source', '.',(select look_values from look_values where look_values_id=OLD.corp_source)),
col5                   = CONCAT('corp_contacts','.',OLD.corp_contacts),
col6                   = CONCAT('corp_address','.',OLD.corp_address),
col7                   = CONCAT('corp_level','.',(select look_values from look_values where look_values_id=OLD.corp_level)),
col8                   = CONCAT('register_yr','.',OLD.register_yr),
col10                  = CONCAT('register_state','.',OLD.register_state),
col11                  = CONCAT('acc_mgr', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id= OLD.acc_mgr)),
col13                  = CONCAT('topic_discussed','.',OLD.topic_discussed),
col14                  = CONCAT('about_corp','.',OLD.about_corp);


 
INSERT INTO log_global 
set
last_update 	       = now(),
updater                = (select concat(firstname,' ',lastname) as name from emp where emp_id=(case when (NEW.updated_by = 0) then 1 else NEW.updated_by end)),
table_update           = 'prosp_corp',
actions		           = 'N',	
table_id	           = CONCAT('idprosp_corp', '.',NEW.idprosp_corp),
col1                   = CONCAT('prosp_corp_type', '.',(select look_values from look_values where look_values_id=NEW.prosp_corp_type)),
col2                   = CONCAT('corp_entity_name', '.',NEW.corp_entity_name),
col4                   = CONCAT('corp_source', '.',(select look_values from look_values where look_values_id=NEW.corp_source)),
col5                   = CONCAT('corp_contacts','.',NEW.corp_contacts),
col6                   = CONCAT('corp_address','.',NEW.corp_address),
col7                   = CONCAT('corp_level','.',(select look_values from look_values where look_values_id=NEW.corp_level)),
col8                   = CONCAT('register_yr','.',NEW.register_yr),
col10                  = CONCAT('register_state','.',NEW.register_state),
col11                  = CONCAT('acc_mgr', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id= NEW.acc_mgr)),
col13                  = CONCAT('topic_discussed','.',NEW.topic_discussed),
col14                  = CONCAT('about_corp','.',NEW.about_corp);

  

END






CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `prosp_corp_after_delete` AFTER DELETE ON `prosp_corp` 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.updated_by = 0) then 1 else OLD.updated_by end)),
table_update           = 'prosp_corp',
actions		           = 'D',	
table_id	           = CONCAT('idprosp_corp', '.',OLD.idprosp_corp),
col1                   = CONCAT('prosp_corp_type', '.',(select look_values from look_values where look_values_id=OLD.prosp_corp_type)),
col2                   = CONCAT('corp_entity_name', '.',OLD.corp_entity_name),
col4                   = CONCAT('corp_source', '.',(select look_values from look_values where look_values_id=OLD.corp_source)),
col5                   = CONCAT('corp_contacts','.',OLD.corp_contacts),
col6                   = CONCAT('corp_address','.',OLD.corp_address),
col7                   = CONCAT('corp_level','.',(select look_values from look_values where look_values_id=OLD.corp_level)),
col8                   = CONCAT('register_yr','.',OLD.register_yr),
col10                  = CONCAT('register_state','.',OLD.register_state),
col11                  = CONCAT('acc_mgr', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id= OLD.acc_mgr)),
col13                  = CONCAT('topic_discussed','.',OLD.topic_discussed),
col14                  = CONCAT('about_corp','.',OLD.about_corp);



END