CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `emp_legal_after_insert` AFTER INSERT ON `emp_legal` 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            = 'emp_legal',
actions			= 'A',	
table_id		= CONCAT('emp_legal_id', '.',NEW.emp_legal_id),
col1			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=NEW.emp_id)),
col2			= CONCAT('doc_type', '.',(select look_values from look_values where look_values_id=NEW.doc_type)),
col4                    = CONCAT('file_name', '.',CONCAT(url,'',(select concat(file_name,'">',filename_attach,'</a>') from storages where idstorage=NEW.idstorage))),
col7			= CONCAT('public', '.',(select public from storages where idstorage=NEW.idstorage)),
col8			= CONCAT('self_view', '.',(select self_view from storages where idstorage=NEW.idstorage)),
col10			= CONCAT('shared', '.',(select shared from storages where idstorage=NEW.idstorage)),
col13                   = CONCAT('expire_dt.',date_format(NEW.expire_dt,'%m-%d-%y'));
 

END







CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `emp_legal_after_update` AFTER UPDATE ON `emp_legal` 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            = 'emp_legal',
actions			= 'O',	
table_id		= CONCAT('emp_legal_id', '.',OLD.emp_legal_id),
col1			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=OLD.emp_id)),
col2			= CONCAT('doc_type', '.',(select look_values from look_values where look_values_id=OLD.doc_type)),
col4                    = CONCAT('file_name', '.',old_filename_attach),
col7			= CONCAT('public', '.',(select public from storages where idstorage=OLD.idstorage)),
col8			= CONCAT('self_view', '.',(select self_view from storages where idstorage=OLD.idstorage)),
col10			= CONCAT('shared', '.',(select shared from storages where idstorage=OLD.idstorage)),
col13                   = CONCAT('expire_dt.',date_format(OLD.expire_dt,'%m-%d-%y'));


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_legal',
actions			= 'N',	
table_id		= CONCAT('emp_legal_id', '.',NEW.emp_legal_id),
col1			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=NEW.emp_id)),
col2			= CONCAT('doc_type', '.',(select look_values from look_values where look_values_id=NEW.doc_type)),
col4                    = CONCAT('file_name', '.',CONCAT(url,'',(select concat(file_name,'">',filename_attach,'</a>') from storages where idstorage=NEW.idstorage))),
col7			= CONCAT('public', '.',(select public from storages where idstorage=NEW.idstorage)),
col8			= CONCAT('self_view', '.',(select self_view from storages where idstorage=NEW.idstorage)),
col10			= CONCAT('shared', '.',(select shared from storages where idstorage=NEW.idstorage)),
col13                   = CONCAT('expire_dt.',date_format(NEW.expire_dt,'%m-%d-%y'));
END









CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `emp_legal_after_delete` AFTER DELETE ON `emp_legal` 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            = 'emp_legal',
actions			= 'D',	
table_id		= CONCAT('emp_legal_id', '.',OLD.emp_legal_id),
col1			= CONCAT('emp_name', '.',(select concat(firstname,' ',lastname) as name from emp where emp_id=OLD.emp_id)),
col2			= CONCAT('doc_type', '.',(select look_values from look_values where look_values_id=OLD.doc_type)),
col4                    = CONCAT('file_name', '.',old_filename_attach),
col7			= CONCAT('public', '.',(select public from storages where idstorage=OLD.idstorage)),
col8			= CONCAT('self_view', '.',(select self_view from storages where idstorage=OLD.idstorage)),
col10			= CONCAT('shared', '.',(select shared from storages where idstorage=OLD.idstorage)),
col13                   = CONCAT('expire_dt.',date_format(OLD.expire_dt,'%m-%d-%y'));


END