CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `comp_reg_dept_dt_after_insert` AFTER INSERT ON `comp_reg_dept_dt` 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 = 0) then 1 else NEW.updated_by end)),
table_update     	= 'comp_reg_dept_dt',
actions			= 'A',	
table_id		= CONCAT('Comp_reg_dept_dt_id', '.',NEW.comp_reg_dept_dt_id),

col1			= CONCAT('Comp_name', '.',(select companyname from company where comp_id=(select comp_id from comp_reg where comp_reg_id=(select comp_reg_id from comp_reg_dept where comp_reg_dept_id=NEW.comp_reg_dept_id)))),

col4			= CONCAT('State', '.',(select state_name from look_state where state_id=(select state_id from comp_reg where comp_reg_id=(select comp_reg_id from comp_reg_dept where comp_reg_dept_id=NEW.comp_reg_dept_id)) )),

col7                    = CONCAT('Dept', '.',(select look_values from look_values where look_values_id=(select dept_id from comp_reg_dept where comp_reg_dept_id=NEW.comp_reg_dept_id))),   	
col10                   = CONCAT('Reg_number', '.',(select reg_number from comp_reg_dept where comp_reg_dept_id=NEW.comp_reg_dept_id)),	
col13			= CONCAT('Comp_open_dt', '.',date_format(NEW.comp_open_dt,'%m-%d-%y')),
col14                   = CONCAT('Comp_close_dt', '.',date_format(NEW.comp_close_dt,'%m-%d-%y'));


END






CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `comp_reg_dept_dt_after_update` AFTER UPDATE ON `comp_reg_dept_dt` 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     	= 'comp_reg_dept_dt',
actions			= 'O',	
table_id		= CONCAT('Comp_reg_dept_dt_id', '.',OLD.comp_reg_dept_dt_id),

col1			= CONCAT('Comp_name', '.',(select companyname from company where comp_id=(select comp_id from comp_reg where comp_reg_id=(select comp_reg_id from comp_reg_dept where comp_reg_dept_id=OLD.comp_reg_dept_id)))),

col4			= CONCAT('State', '.',(select state_name from look_state where state_id=(select state_id from comp_reg where comp_reg_id=(select comp_reg_id from comp_reg_dept where comp_reg_dept_id=OLD.comp_reg_dept_id)) )),

col7                    = CONCAT('Dept', '.',(select look_values from look_values where look_values_id=(select dept_id from comp_reg_dept where comp_reg_dept_id=OLD.comp_reg_dept_id))),   	
col10                   = CONCAT('Reg_number', '.',(select reg_number from comp_reg_dept where comp_reg_dept_id=OLD.comp_reg_dept_id)),	
col13			= CONCAT('Comp_open_dt', '.',date_format(OLD.comp_open_dt,'%m-%d-%y')),
col14                   = CONCAT('Comp_close_dt', '.',date_format(OLD.comp_close_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.updated_by = 0) then 1 else NEW.updated_by end)),
table_update     	= 'comp_reg_dept_dt',
actions			= 'N',	
table_id		= CONCAT('Comp_reg_dept_dt_id', '.',NEW.comp_reg_dept_dt_id),

col1			= CONCAT('Comp_name', '.',(select companyname from company where comp_id=(select comp_id from comp_reg where comp_reg_id=(select comp_reg_id from comp_reg_dept where comp_reg_dept_id=NEW.comp_reg_dept_id)))),

col4			= CONCAT('State', '.',(select state_name from look_state where state_id=(select state_id from comp_reg where comp_reg_id=(select comp_reg_id from comp_reg_dept where comp_reg_dept_id=NEW.comp_reg_dept_id)) )),

col7                    = CONCAT('Dept', '.',(select look_values from look_values where look_values_id=(select dept_id from comp_reg_dept where comp_reg_dept_id=NEW.comp_reg_dept_id))),   	
col10                   = CONCAT('Reg_number', '.',(select reg_number from comp_reg_dept where comp_reg_dept_id=NEW.comp_reg_dept_id)),	
col13			= CONCAT('Comp_open_dt', '.',date_format(NEW.comp_open_dt,'%m-%d-%y')),
col14                   = CONCAT('Comp_close_dt', '.',date_format(NEW.comp_close_dt,'%m-%d-%y'));


END









CREATE DEFINER=`aerion`@`192.180.1.5` TRIGGER `comp_reg_dept_dt_after_delete` AFTER DELETE ON `comp_reg_dept_dt` 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     	= 'comp_reg_dept_dt',
actions			= 'D',	
table_id		= CONCAT('Comp_reg_dept_dt_id', '.',OLD.comp_reg_dept_dt_id),

col1			= CONCAT('Comp_name', '.',(select companyname from company where comp_id=(select comp_id from comp_reg where comp_reg_id=(select comp_reg_id from comp_reg_dept where comp_reg_dept_id=OLD.comp_reg_dept_id)))),

col4			= CONCAT('State', '.',(select state_name from look_state where state_id=(select state_id from comp_reg where comp_reg_id=(select comp_reg_id from comp_reg_dept where comp_reg_dept_id=OLD.comp_reg_dept_id)) )),

col7                    = CONCAT('Dept', '.',(select look_values from look_values where look_values_id=(select dept_id from comp_reg_dept where comp_reg_dept_id=OLD.comp_reg_dept_id))),   	
col10                   = CONCAT('Reg_number', '.',(select reg_number from comp_reg_dept where comp_reg_dept_id=OLD.comp_reg_dept_id)),	
col13			= CONCAT('Comp_open_dt', '.',date_format(OLD.comp_open_dt,'%m-%d-%y')),
col14                   = CONCAT('Comp_close_dt', '.',date_format(OLD.comp_close_dt,'%m-%d-%y'));




END