0

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,

  • I have figured out the solution for the issue. I was just needed to get the id like NEW.StudentID – Pawan Tiwari Sep 6 at 13:50
  • You can answer your own question :-) - no points though :-( – Vérace Sep 6 at 15:46
0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.