CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `prosp_attach_after_insert` AFTER INSERT ON `prosp_attach` FOR EACH ROW begin

DECLARE url varchar(95);
DECLARE furl varchar(95);
DECLARE filename_attach varchar(95);
DECLARE old_filename_attach varchar(95);

set url=CONCAT('<a href="','',(select table_summary from look_table where table_name="file_url"));
set furl=(select table_summary from look_table where table_name="file_url");
set filename_attach=CONCAT(furl,'',(select file_name from storages where idstorage=NEW.idstorage)); 
set old_filename_attach=concat(furl,'',(select file_name from storages where idstorage=NEW.idstorage));




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            = 'prosp_attach',
actions			= 'A',	
table_id		= CONCAT('idprosp_attach', '.',NEW.idprosp_attach),
col1                    = CONCAT('file_name', '.',CONCAT(url,'',(select concat(file_name,'">',filename_attach,'</a>') from storages where idstorage=NEW.idstorage))),
col4                    = CONCAT('prosp_ref', '.',(select concat(prosp_first_name,' ',prosp_last_name) from prosp_consult  where prosp_consult_id=NEW.prosp_consult_id)),
col7                   = CONCAT('updated_for_ref', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=(select(case when (updated_for_ref_id = 0) then 1 else updated_for_ref_id end ) from storages where idstorage=NEW.idstorage))),
col8                    = CONCAT('updated_for_ref_table', '.',(select updated_for_ref_table from storages where idstorage=NEW.idstorage)),
col10			= CONCAT('public', '.',(select public from storages where idstorage=NEW.idstorage)),
col11			= CONCAT('self_view', '.',(select self_view from storages where idstorage=NEW.idstorage)),
col13			= CONCAT('shared', '.',(select shared from storages where idstorage=NEW.idstorage));


END






CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `prosp_attach_after_update` AFTER UPDATE ON `prosp_attach` FOR EACH ROW begin

DECLARE url varchar(95);
DECLARE furl varchar(95);
DECLARE filename_attach varchar(95);
DECLARE old_filename_attach varchar(95);

set url=CONCAT('<a href="','',(select table_summary from look_table where table_name="file_url"));
set furl=(select table_summary from look_table where table_name="file_url");
set filename_attach=CONCAT(furl,'',(select file_name from storages where idstorage=NEW.idstorage)); 
set old_filename_attach=concat(furl,'',(select file_name from storages where idstorage=OLD.idstorage));

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            = 'prosp_attach',
actions			= 'O',	
table_id		= CONCAT('idprosp_attach', '.',OLD.idprosp_attach),
col1                    = CONCAT('file_name', '.',old_filename_attach),
col4                    = CONCAT('prosp_ref', '.',(select concat(prosp_first_name,' ',prosp_last_name) from prosp_consult  where prosp_consult_id=OLD.prosp_consult_id)),
col7                   = CONCAT('updated_for_ref', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=(select(case when (updated_for_ref_id = 0) then 1 else updated_for_ref_id end ) from storages where idstorage=OLD.idstorage))),
col8                    = CONCAT('updated_for_ref_table', '.',(select updated_for_ref_table from storages where idstorage=OLD.idstorage)),
col10			= CONCAT('public', '.',(select public from storages where idstorage=OLD.idstorage)),
col11			= CONCAT('self_view', '.',(select self_view from storages where idstorage=OLD.idstorage)),
col13			= CONCAT('shared', '.',(select shared from storages where idstorage=OLD.idstorage));




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            = 'prosp_attach',
actions			= 'N',	
table_id		= CONCAT('idprosp_attach', '.',NEW.idprosp_attach),
col1                    = CONCAT('file_name', '.',CONCAT(url,'',(select concat(file_name,'">',filename_attach,'</a>') from storages where idstorage=NEW.idstorage))),
col4                    = CONCAT('prosp_ref', '.',(select concat(prosp_first_name,' ',prosp_last_name) from prosp_consult  where prosp_consult_id=NEW.prosp_consult_id)),
col7                   = CONCAT('updated_for_ref', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=(select(case when (updated_for_ref_id = 0) then 1 else updated_for_ref_id end ) from storages where idstorage=NEW.idstorage))),
col8                    = CONCAT('updated_for_ref_table', '.',(select updated_for_ref_table from storages where idstorage=NEW.idstorage)),
col10			= CONCAT('public', '.',(select public from storages where idstorage=NEW.idstorage)),
col11			= CONCAT('self_view', '.',(select self_view from storages where idstorage=NEW.idstorage)),
col13			= CONCAT('shared', '.',(select shared from storages where idstorage=NEW.idstorage));


END







CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `prosp_attach_after_delete` AFTER DELETE ON `prosp_attach` FOR EACH ROW begin


DECLARE furl varchar(95);
DECLARE old_filename_attach varchar(95);

set furl=(select table_summary from look_table where table_name="file_url");
set old_filename_attach=concat(furl,'',(select file_name from storages where idstorage=OLD.idstorage));

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            = 'prosp_attach',
actions			= 'D',	
table_id		= CONCAT('idprosp_attach', '.',OLD.idprosp_attach),
col1                    = CONCAT('file_name', '.',old_filename_attach),
col4                    = CONCAT('prosp_ref', '.',(select concat(prosp_first_name,' ',prosp_last_name) from prosp_consult  where prosp_consult_id=OLD.prosp_consult_id)),
col7                   = CONCAT('updated_for_ref', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=(select(case when (updated_for_ref_id = 0) then 1 else updated_for_ref_id end ) from storages where idstorage=OLD.idstorage))),
col8                    = CONCAT('updated_for_ref_table', '.',(select updated_for_ref_table from storages where idstorage=OLD.idstorage)),
col10			= CONCAT('public', '.',(select public from storages where idstorage=OLD.idstorage)),
col11			= CONCAT('self_view', '.',(select self_view from storages where idstorage=OLD.idstorage)),
col13			= CONCAT('shared', '.',(select shared from storages where idstorage=OLD.idstorage));


END