Mysql – How to insert only one row which is going to update

MySQLtrigger

I want to fire a trigger in Mysql DB before update the row. And I want to insert the entire row value in another table.

My trigger fires, but the problem is, it inserts all the data from old table to new one.

My query is: How can I activate the trigger for particular id in old table?

I've tried the various links but they are not useful for me.

My attempt:

CREATE DEFINER=`root`@`%` TRIGGER before_trn_student_profile_update 
    BEFORE UPDATE ON trn_student_profile
    FOR EACH ROW 
BEGIN
    INSERT INTO trn_student_profile_history (
`StudentID`,
`SchoolID`,
`SessionID`,
`ClassID`,
`SectionID`,
`AdmissionDate`,
`AdmissionNumber`,
`ReferenceNo`,
`StudentTypeID`,
`StudentName`,
`FatherTitle`,
`FatherName`,
`IsFatherGuardian`,
`MotherTitle`,
`MotherName`,
`parent_address`,
`DateOfBirth`,
`Gender`,
`SocialCategoryID`,
`ReligionID`,
`MotherToungeID`,
`LocalityName`,
`BelongToBPL`,
`BelongToDisadvantageGroup`,
`IsBelongToDisadvantageGroup`,
`IsFreeEduction`,
`PreviousYearClassID`,
`PreviousYearStatusID`,
`PreviousYearAttendanceDays`,
`MeduimOfInstructionID`,
`DisabilityTypeID`,
`IsLastExaminationAppeared`,
`IsLastExaminationPassed`,
`LastExaminationPercentage`,
`SchoolingStatusID`,
`StreamID`,
`CWSANFacilityID`,
`UniformSetID`,
`IsFreeTextBooks`,
`IsFreeTransport`,
`IsFreeEscortFacility`,
`IsMDMBenificiary`,
`FreeHostelFacilityID`,
`SpecialTraining`,
`IsFolicAcidTabletReceived`,
`IsDewormingTablets`,
`IsVitaminASupplement`,
`VocationCourseTradeID`,
`JobRoleID`,
`NSFQLevelID`,
`StudentOpted`,
`PlacementStatus`,
`SalaryOffered`,
`BankAccountNumber`,
`BranchIFSCCode`,
`MobileNumber`,
`Email`,
`IsActive`,
`P1Status`,
`P2Status`,
`DataStatus`,
`CreatedByUserID`,
`CreatedDate`,
`UpdatedByUserID`,
`UpdatedDate`,
`IsChildHomeless`,
`GradeOptedForVocationalCourse`,
`SocialCatagoryOther`,
`ReligionOther`,
`MotherTongueOther`,
`MediumOfInstructionOther`,
`TypeOfDisabilityOther`,
`SchoolingStatusOther`,
`StreamOther`,
`VocationCourseTradeOther`,
`cswnOther`,
`Reason`,
`EnrolmentType`,
`IsVocationalRequired`,
`unique_id`,
`virtual_id`,
`enrollment_id`,
`unique_student_number`,
`reason_id_for_not_having_adhar`,
`student_first_name`,
`student_middle_name`,
`student_last_name`,
`student_subCategory_Id`,
`prior_status_id`,
`state_id`,
`district_id`,
`block_id`,
`habitation_id`,
`student_Opted_Id`,
`action_status`)
select 
`StudentID`,
`SchoolID`,
`SessionID`,
`ClassID`,
`SectionID`,
`AdmissionDate`,
`AdmissionNumber`,
`ReferenceNo`,
`StudentTypeID`,
`StudentName`,
`FatherTitle`,
`FatherName`,
`IsFatherGuardian`,
`MotherTitle`,
`MotherName`,
`parent_address`,
`DateOfBirth`,
`Gender`,
`SocialCategoryID`,
`ReligionID`,
`MotherToungeID`,
`LocalityName`,
`BelongToBPL`,
`BelongToDisadvantageGroup`,
`IsBelongToDisadvantageGroup`,
`IsFreeEduction`,
`PreviousYearClassID`,
`PreviousYearStatusID`,
`PreviousYearAttendanceDays`,
`MeduimOfInstructionID`,
`DisabilityTypeID`,
`IsLastExaminationAppeared`,
`IsLastExaminationPassed`,
`LastExaminationPercentage`,
`SchoolingStatusID`,
`StreamID`,
`CWSANFacilityID`,
`UniformSetID`,
`IsFreeTextBooks`,
`IsFreeTransport`,
`IsFreeEscortFacility`,
`IsMDMBenificiary`,
`FreeHostelFacilityID`,
`SpecialTraining`,
`IsFolicAcidTabletReceived`,
`IsDewormingTablets`,
`IsVitaminASupplement`,
`VocationCourseTradeID`,
`JobRoleID`,
`NSFQLevelID`,
`StudentOpted`,
`PlacementStatus`,
`SalaryOffered`,
`BankAccountNumber`,
`BranchIFSCCode`,
`MobileNumber`,
`Email`,
`IsActive`,
`P1Status`,
`P2Status`,
`DataStatus`,
`CreatedByUserID`,
`CreatedDate`,
`UpdatedByUserID`,
`UpdatedDate`,
`IsChildHomeless`,
`GradeOptedForVocationalCourse`,
`SocialCatagoryOther`,
`ReligionOther`,
`MotherTongueOther`,
`MediumOfInstructionOther`,
`TypeOfDisabilityOther`,
`SchoolingStatusOther`,
`StreamOther`,
`VocationCourseTradeOther`,
`cswnOther`,
`Reason`,
`EnrolmentType`,
`IsVocationalRequired`,
`unique_id`,
`virtual_id`,
`enrollment_id`,
`unique_student_number`,
`reason_id_for_not_having_adhar`,
`student_first_name`,
`student_middle_name`,
`student_last_name`,
`student_subCategory_Id`,
`prior_status_id`,
`state_id`,
`district_id`,
`block_id`,
`habitation_id`,
`student_Opted_Id`,
`action_status` '1' from trn_student_profile
 where trn_student_profile.StudentID != NEW.StudentID AND trn_student_profile.SchoolID != NEW.SchoolID; 
END

Thanks,

Best Answer

And I want to insert the entire row value in another table.

Be more precise -- which "row value" are you referring to? Assuming you want to copy the before-update values from profile to profile_history, then:

from trn_student_profile
where trn_student_profile.StudentID != NEW.StudentID
  AND trn_student_profile.SchoolID != NEW.SchoolID;

-->

from OLD
where OLD.StudentID != NEW.StudentID
  AND OLD.SchoolID  != NEW.SchoolID;

And... Perhaps you want OR instead of AND?

Related Solutions

DB2 Trigger: only update current row

Your UPDATE statement is wrong. It is updating all the records in the table because it doesn't have any WHERE clause. If you want to update only the current row, you need to use the updated/inserted records (like in Jack's example: REFERENCING NEW AS N_ROW and use it as filter).

MySQL – How to Update or Insert into Dynamic Columns

Unfortunately, Dynamic SQL is not permitted in Triggers. Notwithstanding, what you are asking for is still possible, but you will have to apply some elbow grease.

I wrote an answer to the post Disable trigger for just one table. What I did was create a user variable @TRIGGER_DISABLED as a flag to control if the code within the trigger was to be executed.

In your particular case, you will need 9 flags. You will also need a BEFORE UPDATE trigger:

DELIMITER $$
CREATE TRIGGER `setup_record_time` BEFORE UPDATE ON `my_table`
FOR EACH ROW 
BEGIN
  SET @rec_col1 = IF(NEW.col1=OLD.col1,0,1);
  SET @rec_col2 = IF(NEW.col2=OLD.col2,0,1);
  SET @rec_col3 = IF(NEW.col3=OLD.col3,0,1);
  SET @rec_col4 = IF(NEW.col4=OLD.col4,0,1);
  SET @rec_col5 = IF(NEW.col5=OLD.col5,0,1);
  SET @rec_col6 = IF(NEW.col6=OLD.col6,0,1);
  SET @rec_col7 = IF(NEW.col7=OLD.col7,0,1);
  SET @rec_col8 = IF(NEW.col8=OLD.col8,0,1);
  SET @rec_col9 = IF(NEW.col9=OLD.col9,0,1);
END $$
DELIMITER $$

The AFTER UPDATE trigger looks like this:

DELIMITER $$
CREATE TRIGGER `record_time` AFTER UPDATE ON `my_table`
FOR EACH ROW 
BEGIN
  IF @rec_col1 = 1 THEN INSERT INTO `record_changes` (col1) values (CUR_DATE()); END IF;
  IF @rec_col2 = 1 THEN INSERT INTO `record_changes` (col2) values (CUR_DATE()); END IF;
  IF @rec_col3 = 1 THEN INSERT INTO `record_changes` (col3) values (CUR_DATE()); END IF;
  IF @rec_col4 = 1 THEN INSERT INTO `record_changes` (col4) values (CUR_DATE()); END IF;
  IF @rec_col5 = 1 THEN INSERT INTO `record_changes` (col5) values (CUR_DATE()); END IF;
  IF @rec_col6 = 1 THEN INSERT INTO `record_changes` (col6) values (CUR_DATE()); END IF;
  IF @rec_col7 = 1 THEN INSERT INTO `record_changes` (col7) values (CUR_DATE()); END IF;
  IF @rec_col8 = 1 THEN INSERT INTO `record_changes` (col8) values (CUR_DATE()); END IF;
  IF @rec_col9 = 1 THEN INSERT INTO `record_changes` (col9) values (CUR_DATE()); END IF;
END $$
DELIMITER $$

Just to be on the safe side, you should initialize these 9 user variables with 0.

SET @rec_col1 = 0;
SET @rec_col2 = 0;
SET @rec_col3 = 0;
SET @rec_col4 = 0;
SET @rec_col5 = 0;
SET @rec_col6 = 0;
SET @rec_col7 = 0;
SET @rec_col8 = 0;
SET @rec_col9 = 0;
UPDATE mytable ... ;

That way, your session dictates the trigger's behavior.

Give it a Try !!!

Best Answer

Related Solutions

DB2 Trigger: only update current row

MySQL – How to Update or Insert into Dynamic Columns

Related Question