CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `immi_attach_after_insert` AFTER INSERT ON `immi_attach` FOR EACH ROW begin

DECLARE url varchar(95);
DECLARE furl varchar(95);
DECLARE 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));

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            = 'immi_attach',
actions			= 'A',	
table_id		= CONCAT('idimmi_attach', '.',NEW.idimmi_attach),
col1                    = CONCAT('file_name', '.',CONCAT(url,'',(select concat(file_name,'">',filename_attach,'</a>') from storages where idstorage=NEW.idstorage))),
col4                    = CONCAT('Status', '.',(select look_values from look_values where look_values_id=(select rec_status from immi_status where status_id=NEW.status_id))),
col5                   = CONCAT('updated_for_ref_id', '.',(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))),
col7                    = CONCAT('updated_for_ref_table', '.',(select updated_for_ref_table from storages where idstorage=NEW.idstorage)),
col8			= CONCAT('public', '.',(select public from storages where idstorage=NEW.idstorage)),
col10			= 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 `immi_attach_after_update` AFTER UPDATE ON `immi_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            = 'immi_attach',
actions			= 'O',	
table_id		= CONCAT('idimmi_attach', '.',OLD.idimmi_attach),
col1                    = CONCAT('file_name', '.',old_filename_attach),
col4                    = CONCAT('Status', '.',(select look_values from look_values where look_values_id=(select rec_status from immi_status where status_id=OLD.status_id))),
col5                   = CONCAT('updated_for_ref_id', '.',(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))),

col7                    = CONCAT('updated_for_ref_table', '.',(select updated_for_ref_table from storages where idstorage=OLD.idstorage)),


col8			= CONCAT('public', '.',(select public from storages where idstorage=OLD.idstorage)),
col10			= 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            = 'immi_attach',
actions			= 'N',	
table_id		= CONCAT('idimmi_attach', '.',NEW.idimmi_attach),
col1                    = CONCAT('file_name', '.',CONCAT(url,'',(select concat(file_name,'">',filename_attach,'</a>') from storages where idstorage=NEW.idstorage))),
col4                    = CONCAT('Status', '.',(select look_values from look_values where look_values_id=(select rec_status from immi_status where status_id=NEW.status_id))),
col5                   = CONCAT('updated_for_ref_id', '.',(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))),

col7                    = CONCAT('updated_for_ref_table', '.',(select updated_for_ref_table from storages where idstorage=NEW.idstorage)),


col8			= CONCAT('public', '.',(select public from storages where idstorage=NEW.idstorage)),
col10			= 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 `immi_attach_after_delete` AFTER DELETE ON `immi_attach` FOR EACH ROW begin

DECLARE old_filename_attach varchar(95);
DECLARE furl 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            = 'immi_attach',
actions			= 'D',	
table_id		= CONCAT('idimmi_attach', '.',OLD.idimmi_attach),
col1                    = CONCAT('file_name', '.',old_filename_attach),
col4                    = CONCAT('Status', '.',(select look_values from look_values where look_values_id=(select rec_status from immi_status where status_id=OLD.status_id))),
col5                   = CONCAT('updated_for_ref_id', '.',(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))),

col7                    = CONCAT('updated_for_ref_table', '.',(select updated_for_ref_table from storages where idstorage=OLD.idstorage)),


col8			= CONCAT('public', '.',(select public from storages where idstorage=OLD.idstorage)),
col10			= CONCAT('self_view', '.',(select self_view from storages where idstorage=OLD.idstorage)),
col13			= CONCAT('shared', '.',(select shared from storages where idstorage=OLD.idstorage));


END