MySQL Forums
Forum List  »  Triggers

Trigger is running equal to times, the no of columns updated in the row
Posted by: Vikas Thakur
Date: September 15, 2014 12:51AM

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 ...

Options: ReplyQuote


Subject
Views
Written By
Posted
Trigger is running equal to times, the no of columns updated in the row
2246
September 15, 2014 12:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.