CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `storages_after_insert` AFTER INSERT ON `storages` FOR EACH ROW begin
 
DECLARE url varchar(95);
DECLARE furl varchar(95);
DECLARE logoname 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 logoname=CONCAT(furl,'',(select file_name from storages where idstorage=NEW.idstorage));

IF(NEW.tag_id=(select look_values_id from look_values where look_values='Uploads'))THEN
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     	    = 'storages',
actions			    = 'A',
table_id		    = CONCAT('idstorage', '.',NEW.idstorage),
col1                        = CONCAT('file_name', '.',CONCAT(url,'',(select concat(file_name,'">',logoname,'</a>') from storages where idstorage=NEW.idstorage),'\n')),
col2			    = CONCAT('file_size', '.',NEW.file_size,'\n'),
col4			    = CONCAT('owner_name', '.',(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.owner_id)),
col5			    = CONCAT('create_dt', '.',CONCAT(DATE_FORMAT(NEW.create_dt, '%m-%d-%y'),'  ',DATE_FORMAT(NEW.create_dt,'%H:%i:%s'))),
col6			    = CONCAT('ext_type', '.',NEW.ext_type),
col7			    = CONCAT('public', '.',NEW.public),
col8			    = CONCAT('self_view', '.',NEW.self_view),
col9			    = CONCAT('shared', '.',NEW.shared),
col10			    = CONCAT('file_summary', '.',NEW.file_summary),
col11			    = CONCAT('updated_for_ref', '.',(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.updated_for_ref_id)),
col13			    = CONCAT('updated_for_ref_table', '.',NEW.updated_for_ref_table),
col14			    = CONCAT('tag', '.',(select look_values from look_values where look_values_id = NEW.tag_id));


END IF; 

END







CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `storages_after_update` AFTER UPDATE ON `storages` FOR EACH ROW begin


DECLARE url varchar(95);
DECLARE furl varchar(95);
DECLARE logoname varchar(95);
DECLARE oldlogoname 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 logoname=CONCAT(furl,'',(select file_name from storages where idstorage=NEW.idstorage));
set oldlogoname=concat(furl,'',(select file_name from storages where idstorage=OLD.idstorage));

IF(NEW.tag_id=(select look_values_id from look_values where look_values='Uploads'))THEN

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           	    = 'storages',
actions			    = 'O',
table_id		    = CONCAT('idstorage', '.',OLD.idstorage),
col1                        = CONCAT('file_name', '.',oldlogoname),
col2			    = CONCAT('file_size', '.',OLD.file_size,'\n'),
col4			    = CONCAT('owner_name', '.',(select concat(firstname, ' ',lastname) from emp where emp_id = OLD.owner_id)),
col5			    = CONCAT('create_dt', '.',CONCAT(DATE_FORMAT(OLD.create_dt, '%m-%d-%y'),'  ',DATE_FORMAT(OLD.create_dt,'%H:%i:%s'))),
col6			    = CONCAT('ext_type', '.',OLD.ext_type),
col7			    = CONCAT('public', '.',OLD.public),
col8			    = CONCAT('self_view', '.',OLD.self_view),
col9			    = CONCAT('shared', '.',OLD.shared),
col10			    = CONCAT('file_summary', '.',OLD.file_summary),
col11			    = CONCAT('updated_for_ref', '.',(select concat(firstname, ' ',lastname) from emp where emp_id = OLD.updated_for_ref_id)),
col13			    = CONCAT('updated_for_ref_table', '.',OLD.updated_for_ref_table),
col14			    = CONCAT('tag', '.',(select look_values from look_values where look_values_id = OLD.tag_id));



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     	    = 'storages',
actions			    = 'N',
table_id		    = CONCAT('idstorage', '.',NEW.idstorage),
col1                        = CONCAT('file_name', '.',CONCAT(url,'',(select concat(file_name,'">',logoname,'</a>') from storages where idstorage=NEW.idstorage),'\n')),
col2			    = CONCAT('file_size', '.',NEW.file_size,'\n'),
col4			    = CONCAT('owner_name', '.',(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.owner_id)),
col5			    = CONCAT('create_dt', '.',CONCAT(DATE_FORMAT(NEW.create_dt, '%m-%d-%y'),'  ',DATE_FORMAT(NEW.create_dt,'%H:%i:%s'))),
col6			    = CONCAT('ext_type', '.',NEW.ext_type),
col7			    = CONCAT('public', '.',NEW.public),
col8			    = CONCAT('self_view', '.',NEW.self_view),
col9			    = CONCAT('shared', '.',NEW.shared),
col10			    = CONCAT('file_summary', '.',NEW.file_summary),
col11			    = CONCAT('updated_for_ref', '.',(select concat(firstname, ' ',lastname) from emp where emp_id = NEW.updated_for_ref_id)),
col13			    = CONCAT('updated_for_ref_table', '.',NEW.updated_for_ref_table),
col14			    = CONCAT('tag', '.',(select look_values from look_values where look_values_id = NEW.tag_id));


END IF; 

END







CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `storages_after_delete` AFTER DELETE ON `storages` FOR EACH ROW begin

DECLARE del_filename varchar(95);

set del_filename=(select file_name from storages where idstorage=OLD.idstorage);

IF(OLD.tag_id=(select look_values_id from look_values where look_values='Uploads'))THEN

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           	    = 'storages',
actions			    = 'D',
table_id		    = CONCAT('idstorage', '.',OLD.idstorage),
col1                        = CONCAT('file_name', '.',del_filename),
col2			    = CONCAT('file_size', '.',OLD.file_size,'\n'),
col4			    = CONCAT('owner_name', '.',(select concat(firstname, ' ',lastname) from emp where emp_id = OLD.owner_id)),
col5			    = CONCAT('create_dt', '.',CONCAT(DATE_FORMAT(OLD.create_dt, '%m-%d-%y'),'  ',DATE_FORMAT(OLD.create_dt,'%H:%i:%s'))),
col6			    = CONCAT('ext_type', '.',OLD.ext_type),
col7			    = CONCAT('public', '.',OLD.public),
col8			    = CONCAT('self_view', '.',OLD.self_view),
col9			    = CONCAT('shared', '.',OLD.shared),
col10			    = CONCAT('file_summary', '.',OLD.file_summary),
col11			    = CONCAT('updated_for_ref', '.',(select concat(firstname, ' ',lastname) from emp where emp_id = OLD.updated_for_ref_id)),
col13			    = CONCAT('updated_for_ref_table', '.',OLD.updated_for_ref_table),
col14			    = CONCAT('tag', '.',(select look_values from look_values where look_values_id = OLD.tag_id));

END IF;

END