Trigger is running equal to times, the no of columns updated in the row
Hello,
This trigger inserts the row in the history table if any of the column of the transaction table is updated. But if update multiple columns in the transaction table, trigger inserts the rows equal to columns updated in the transaction table.
Please explain me, how is it working.
Moreover, if i am updating the column of particular row of transaction table and inserting the same value, the trigger is still executing and inserting the blank rows.
Here is my code:
USE `cctns_state_db`;
Drop trigger if exists t_person_edu_ext_AUPD;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `t_person_edu_ext_AUPD` AFTER UPDATE ON `t_person_edu_ext` FOR EACH ROW
begin
DECLARE V_SEQUENCE_NO BIGINT(20);
DECLARE V_COLUMN_NAME VARCHAR(500);
DECLARE V_COLUMN_VALUE_NEW VARCHAR(5000);
DECLARE V_COLUMN_VALUE_OLD VARCHAR(5000);
SET V_SEQUENCE_NO = (SELECT IF(MAX(SEQUENCE_NO) IS NULL,1,MAX(SEQUENCE_NO)+1) FROM h_t_crc_history);
if not (new.EDU_QUAL_CD = old.EDU_QUAL_CD) then
begin
SET V_COLUMN_NAME = 'Educational Qualification';
SELECT edu_qual FROM m_educational_qualification where edu_qual_cd= new.edu_qual_cd and lang_cd=new.lang_cd and record_status='C' into V_COLUMN_VALUE_NEW;
SELECT edu_qual FROM m_educational_qualification where edu_qual_cd= old.edu_qual_cd and lang_cd=new.lang_cd and record_status='C' into V_COLUMN_VALUE_OLD;
end;
end if;
if not (new.Board_UNI_CD = old.Board_UNI_CD) then
begin
set V_COLUMN_NAME = 'Board/University';
SELECT board_uni_name FROM m_board_uni_ext where board_uni_cd= new.Board_UNI_CD and lang_cd=new.lang_cd and record_status='C' into V_COLUMN_VALUE_NEW;
SELECT board_uni_name FROM m_board_uni_ext where board_uni_cd= old.Board_UNI_CD and lang_cd=new.lang_cd and record_status='C' into V_COLUMN_VALUE_OLD;
end;
end if;
if not (new.School_College = old.School_College) then
begin
set V_COLUMN_NAME = 'School/College';
set V_COLUMN_VALUE_NEW = new.School_College;
set V_COLUMN_VALUE_OLD = old.School_College;
end;
end if;
if not (new.SUBJECT_COURSE_CD = old.SUBJECT_COURSE_CD) then
begin
set V_COLUMN_NAME = 'Subject/ Course';
SELECT SUBJECT_COURSE FROM m_subject_courses_ext where SUBJECT_COURSE_CD= new.SUBJECT_COURSE_CD and lang_cd=new.lang_cd and record_status='C' into V_COLUMN_VALUE_NEW;
SELECT SUBJECT_COURSE FROM m_subject_courses_ext where SUBJECT_COURSE_CD= old.SUBJECT_COURSE_CD and lang_cd=new.lang_cd and record_status='C' into V_COLUMN_VALUE_OLD;
end;
end if;
if not (new.Percentage = old.Percentage) then
begin
set V_COLUMN_NAME = 'Percentage';
set V_COLUMN_VALUE_NEW = new.Percentage;
set V_COLUMN_VALUE_OLD = old.Percentage;
end;
end if;
insert into h_t_crc_history(
SEQUENCE_NO,LANG_CD,T_SEQUENCE_NO,PERSON_CODE,TAB_NAME,COLUMN_NAME,COLUMN_VALUE_OLD,
COLUMN_VALUE_NEW,RECORD_UPDATED_ON,RECORD_UPDATED_BY,ORIGINAL_RECORD,OPER)
value (
V_SEQUENCE_NO,NEW.LANG_CD,NEW.EDUCATION_CD,
NEW.PERSON_CODE,'Educational Qualification', V_COLUMN_NAME, V_COLUMN_VALUE_OLD, V_COLUMN_VALUE_NEW, NEW.RECORD_UPDATED_ON, NEW.RECORD_UPDATED_BY,
NEW.ORIGINAL_RECORD,'Updating');
end
And here is my table structure of history table:
DROP TABLE IF EXISTS `abc`.`h_t_crc_history`;
CREATE TABLE `cctns_state_db`.`h_t_crc_history` (
`SEQUENCE_NO` bigint(20) DEFAULT NULL,
`LANG_CD` int(11) DEFAULT NULL,
`T_SEQUENCE_NO` bigint(20) DEFAULT NULL,
`PERSON_CODE` bigint(20) DEFAULT NULL,
`TAB_NAME` varchar(500) DEFAULT NULL,
`COLUMN_NAME` varchar(500) DEFAULT NULL,
`COLUMN_VALUE_OLD` varchar(5000) DEFAULT NULL,
`COLUMN_VALUE_NEW` varchar(5000) DEFAULT NULL,
`RECORD_UPDATED_ON` datetime DEFAULT NULL,
`RECORD_UPDATED_BY` varchar(50) DEFAULT NULL,
`ORIGINAL_RECORD` int(11) DEFAULT NULL,
`OPER` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Thanks in advance ...