CODEWITHSUNDEEP
mysqltriggers
Dashanan
Dashanan

Reputation: 122

How to create interconnected mysql trigger

Here I've two different tables named as usermaster and userdetails. What I want to do is, create a trigger on table usermaster on after insert and in this trigger, I want to update the same table but another row.

 BEGIN
    Declare UserCode varchar(50);
    Declare BasePlan,CurrentPlan decimal(10,4);
    Declare BaseDate,CurrentDate datetime;

    Set UserCode=Trim(new.us_usercode);
    Select ap.ap_planCost,us.us_edtm into BasePlan,BaseDate
from us_uxusermaster us inner join cm_clientmaster cm inner join pl_planlist pl inner join ap_affiliateplan ap 
on us.us_usercode=cm.cm_affiliatecode and pl.pl_id=us.us_planListId and pl.pl_ap_id=ap.ap_id where cm.cm_usercode=UserCode;

    Select ap.ap_planCost,us.us_edtm into CurrentPlan,CurrentDate 
from us_uxusermaster us  inner join pl_planlist pl inner join ap_affiliateplan ap on pl.pl_id=us.us_planListId and pl.pl_ap_id=ap.ap_id where us.us_usercode=UserCode;



    if(CurrentPlan>=BasePlan)
    Then
    If(DateDiff(BaseDate,CurrentDate)<=30)
    Then
    Update us_uxusermaster set us_eby='update' where us_usercode=(Select cm.cm_affiliatecode from cm_clientmaster cm where cm.cm_usercode=UserCode);
    End If;
    End If;

    END

But every time I do so it gives me an error that I cant use the same table for Updation.

So I'm creating two triggers first on after insert of usermaster and in this trigger, It updates table userdetails and one more trigger on after update of table userdetails which updates the table usermaster.

Is this mechanism is right or any other solution exist which is better.

Upvotes: 1

Views: 23

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30819

You can't modify the same table from within the trigger, here's what the documentation says:

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

Also, you seem to have cyclic dependency (via triggers) between usermaster and userdetails tables, something I would strongly recommend moving away from. Another approach would be to perform these updates (from the application) within a transaction.

Upvotes: 1

Related Questions