Radio buttons as trigger events in Mysql

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
User avatar
Jay Webb
Veteran Member
Posts: 55
Joined: 2017-08-26 15:27
Contact:

Radio buttons as trigger events in Mysql

Post by Jay Webb » 2018-05-02 17:01

I’m using AppGini 5.70
I have 4 radio buttons in a table column called; “Activity”, buttons are, Active, Inactive, Resigned and Deceased.
Trying to create MySQL triggers when one of the buttons is selected and form is saved or updated trigger moves data to associate table.
Activity. Active no trigger event.
Activity. Inactive trigger event move data to table Inactive Members.
Activity. Resigned trigger event move date to table Resigned Members.
Activity. Deceased trigger event move data to table Deceased Members
I figured out making the trigger in MySQL but not how to use one of the selected radio buttons as the event trigger I
haven’t figured out.
What we envision, we make happen.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 518
Joined: 2018-04-01 10:12

Re: Radio buttons as trigger events in Mysql

Post by pböttcher » 2018-05-03 06:28

Hi Jay,

you can use just your fieldname.

Assuming you fieldname in the DB for the Activity field is called Activity.

Code: Select all

DELIMITER //

create trigger ##TRIGGERNAME## before insert on ##TABLENAME##
for each row

BEGIN

    CASE new.Activity 
        when 'Inactive' THEN ##YOUR SQL to move data to incative Members table;
        when 'Resigned' THEN ##YOUR SQL to move data to Resigned Members table;
        when 'Deceased' THEN ##YOUR SQL to move data to Deceased Members table;
    END CASE;

END //

DELIMITER ;
the fieldvalues of the data to be inserted can be adressed with the "new" prefix.
Same would apply for the update of existing data.

Hope that works

User avatar
Jay Webb
Veteran Member
Posts: 55
Joined: 2017-08-26 15:27
Contact:

Re: Radio buttons as trigger events in Mysql

Post by Jay Webb » 2018-05-03 17:40

Hello, pböttcher
Thanks for your input, I really appreciate all your help. I was able to create trigger as suggested and it saved to the table but its not firing when of the radio buttons is ticked and "Save Changes" is applied.
Here's my trigger;

Code: Select all

DELIMITER //

CREATE TRIGGER update_activity AFTER INSERT ON MembershipRoster
FOR EACH ROW

BEGIN

    CASE NEW.Activity 
       		 WHEN 'Inactive' THEN INSERT INTO `InactiveMembers` (ID, Activity, MType, ClanID, Region, State, ReNewMonth, LastName, FirstName, SpouseName, Prefix, StreetAddress, City, States, Zip, Country, SNBD, TelePhone, EmailAddress, ReNew, Joined, PD2016, PD2017, PD2018, PD2019, PD2020, PD2021, PD2022, PD2023, PD2024, PD2025, PD2026, PD2027, InvoiceSent, Cancellation, DateTerm, Alert) VALUES (NEW.ID, NEW.Activity, NEW.MType, NEW.ClanID, NEW.Region, NEW.State, NEW.ReNewMonth, NEW.LastName, NEW.FirstName, NEW.SpouseName, NEW.Prefix, NEW.StreetAddress, NEW.City, NEW.States, NEW.Zip, NEW.Country, NEW.SNBD, NEW.TelePhone, NEW.EmailAddress, NEW.ReNew, NEW.Joined, NEW.PD2016, NEW.PD2017, NEW.PD2018, NEW.PD2019, NEW.PD2020, NEW.PD2021, NEW.PD2022, NEW.PD2023, NEW.PD2024, NEW.PD2025, NEW.PD2026, NEW.PD2027, NEW.InvoiceSent, NEW.Cancellation, NEW.DateTerm, NEW.Alert);     
		WHEN 'Resigned' THEN INSERT INTO `ResignedMembers` (ID, Activity, MType, ClanID, Region, State, ReNewMonth, LastName, FirstName, SpouseName, Prefix, StreetAddress, City, States, Zip, Country, SNBD, TelePhone, EmailAddress, ReNew, Joined, PD2016, PD2017, PD2018, PD2019, PD2020, PD2021, PD2022, PD2023, PD2024, PD2025, PD2026, PD2027, InvoiceSent, Cancellation, DateTerm, Alert) VALUES (NEW.ID, NEW.Activity, NEW.MType, NEW.ClanID, NEW.Region, NEW.State, NEW.ReNewMonth, NEW.LastName, NEW.FirstName, NEW.SpouseName, NEW.Prefix, NEW.StreetAddress, NEW.City, NEW.States, NEW.Zip, NEW.Country, NEW.SNBD, NEW.TelePhone, NEW.EmailAddress, NEW.ReNew, NEW.Joined, NEW.PD2016, NEW.PD2017, NEW.PD2018, NEW.PD2019, NEW.PD2020, NEW.PD2021, NEW.PD2022, NEW.PD2023, NEW.PD2024, NEW.PD2025, NEW.PD2026, NEW.PD2027, NEW.InvoiceSent, NEW.Cancellation, NEW.DateTerm, NEW.Alert);	
		WHEN 'Deceased' THEN INSERT INTO `KnownDeceased` (ID, Activity, MType, ClanID, Region, State, ReNewMonth, LastName, FirstName, SpouseName, Prefix, StreetAddress, City, States, Zip, Country, SNBD, TelePhone, EmailAddress, ReNew, Joined, PD2016, PD2017, PD2018, PD2019, PD2020, PD2021, PD2022, PD2023, PD2024, PD2025, PD2026, PD2027, InvoiceSent, Cancellation, DateTerm, Alert) VALUES (NEW.ID, NEW.Activity, NEW.MType, NEW.ClanID, NEW.Region, NEW.State, NEW.ReNewMonth, NEW.LastName, NEW.FirstName, NEW.SpouseName, NEW.Prefix, NEW.StreetAddress, NEW.City, NEW.States, NEW.Zip, NEW.Country, NEW.SNBD, NEW.TelePhone, NEW.EmailAddress, NEW.ReNew, NEW.Joined, NEW.PD2016, NEW.PD2017, NEW.PD2018, NEW.PD2019, NEW.PD2020, NEW.PD2021, NEW.PD2022, NEW.PD2023, NEW.PD2024, NEW.PD2025, NEW.PD2026, NEW.PD2027, NEW.InvoiceSent, NEW.Cancellation, NEW.DateTerm, NEW.Alert);
    END CASE;

END; //

DELIMITER ;
and not sure if back ticks are required in VAULES (NEW.
I also thought ID might be the problem. In MembershipRoster, ID is, primary key and auto increment and the other tables InactiveMembers, ResignedMembers and KnownDeceased, "ID" is primary key and read only, but not auto increment.

Any insight to what I'm doing wrong.

Thanks again.
What we envision, we make happen.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 518
Joined: 2018-04-01 10:12

Re: Radio buttons as trigger events in Mysql

Post by pböttcher » 2018-05-03 18:27

Hi Jay,
what do you mean by read-only? if you cannot add the ID you will not be able to insert the record and the trigger will fail.
can you try to enter a record manually with the SQL statement?
I tried the trigger on a testsytem and it works fine.

Also this trigger will get fired if an insert happens, if you need to catch the update, as somebody will modify an existing record, you need to add a trigger for update.

Replace AFTER INSERT with AFTER UPDATE in the trigger definition and give it another name.

regards
Pascal

User avatar
Jay Webb
Veteran Member
Posts: 55
Joined: 2017-08-26 15:27
Contact:

Re: Radio buttons as trigger events in Mysql

Post by Jay Webb » 2018-05-03 23:29

pböttcher
Awesome, glad you suggested using a trigger.
Yep, AFTER UPDATE is what I did, forgot to change it in my post when I copied my sample text, only want trigger to fire on update, when member goes, inactive, resigns or deceased. Learning I can also use conditions IF and ELSE, but need to read some more and watch some more videos before attempting.

I don't know why I made ID's read only, "mistake" I remove them, trigger fired, but with error, duplicate ID#, did a little reading and it was suggestion to make ID keys auto_increment and that did the trick, trigger fired, data copied.

If I was to delete the row I just used to fire the trigger would I add it to same trigger, make new trigger or add a new one to the table I just updated with first trigger AFTER INSERT.
What we envision, we make happen.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 518
Joined: 2018-04-01 10:12

Re: Radio buttons as trigger events in Mysql

Post by pböttcher » 2018-05-04 06:13

Hi Jay,
glad to see you got it working.
If you need any other action at the time MySQL need to INSERT data, you need to add this action to the same trigger for INSERT. There can only be one trigger. Same applies for update.
If you modify the other tables you should leave the ID from being inserted/updated, but you need to have a unique identifier to know which records links to which one. You could also add a field ID_MembershipRoster into the other tables and put the ID of the source into this field.

User avatar
Jay Webb
Veteran Member
Posts: 55
Joined: 2017-08-26 15:27
Contact:

Re: Radio buttons as trigger events in Mysql

Post by Jay Webb » 2018-05-05 16:17

Hi, pböttcher

After testing, I added back the "Active" button and now when I make an UPDATE it throws an error,

Code: Select all

Case not found for CASE statement
Query:
update `MembershipRoster` set       `Activity`='Active', ----- more stuff------ where `ID`='307'
The above info is displayed because you are currently signed in as the super admin. Other users won't see this.
I believe it's because it's part of the 'Activity' radio button group "CASE NEW.Activity" Active, Inactive, Resigned and Deceased.
I looked for a solution to comment out of the CASE but will need it from one of the other table if the member is reactivated, made "Active".
Question, how to get the CASE in trigger to execute but bypass "Active" I tried a few thing but mysql throws error on trigger save.
What we envision, we make happen.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 518
Joined: 2018-04-01 10:12

Re: Radio buttons as trigger events in Mysql

Post by pböttcher » 2018-05-06 18:12

HI Jay,

note, that you cannot put an update, insert or delete sql for the same table for which the trigger is defined. This is why you get your error (you try to update MembershipRoster while the trigger is defined for MembershipRoster.

Sorry, but I do not fully get the logic behind what you are trying. I understand that you have a member in the MemebershipRoster table.
This member has an Activity which can take 4 values (Active,Inactive, Resigned, Deceased). If an update happens to the member and the activity is not equal to active you will fire the trigger and put the data into on the other tables. This happens with the trigger described above.
Now i assume that the activity is set to this value also in the MemebershipRoster table. Next, if someone puts the activity back to active what would you expect as action?

For the MemebershipRoster table you do not need any changes via the trigger. Appgini will set you activity to active via the radio button update as long as this happens on the page handled through the MemebershipRoster table.

For the other tables you would need to define the required actions.
Assuming you would have a member that you have set to Inactive. Now this memeber is getting active again. If you need to handle the record in the InactiveMembers table you could add a case statement and compare to the old.Activity which reflects the current status of the activity field.

hope that helps

User avatar
Jay Webb
Veteran Member
Posts: 55
Joined: 2017-08-26 15:27
Contact:

Re: Radio buttons as trigger events in Mysql

Post by Jay Webb » 2018-05-07 00:03

Hi Pascal

Here is what I got,
the (IF NEW.columm <=> OLD.column THEN) keeps trigger from firing on changed other than Inactive, Resigned or Deceased. At least I think it will I haven't been able to run it yet because of error.

The part that errors is Active is not in the (CASE NEW.Activity), I tried, WHEN 'Active' THEN (Active); and, IF NEW.Active <=> OLD.Active THEN.
I haven't figured how to add Active into CASE as Default, even if Active is checked, it errors Activity.Active is not defined in CASE.

As far as Deleting after the Trigger has fired I can live with doing it manually after Update from Active to Inactive, Resigned or Deceased.

Code: Select all

DELIMITER //

CREATE TRIGGER update_activity AFTER UPDATE ON MembershipRoster
FOR EACH ROW

BEGIN

	IF NEW.ID <=> OLD.ID || NEW.MType <=> OLD.MType || NEW.ClanID <=> OLD.ClanID || NEW.Region <=> OLD.Region || NEW.State <=> OLD.State || NEW.ReNewMonth <=> OLD.ReNewMonth || NEW.LastName <=> OLD.LastName || NEW.FirstName <=> OLD.FirstName || NEW.SpouseName <=> OLD.SpouseName || NEW.Prefix <=> OLD.Prefix || NEW.StreetAddress <=> OLD.StreetAddress || NEW.City <=> OLD.City || NEW.States <=> OLD.States || NEW.Zip <=> OLD.Zip || NEW.Country <=> OLD.Country || NEW.SNBD <=> OLD.SNBD || NEW.TelePhone <=> OLD.TelePhone || NEW.EmailAddress <=> OLD.EmailAddress || NEW.ReNew <=> OLD.ReNew || NEW.Joined <=> OLD.Joined || NEW.PD1994 <=> OLD.PD1994 || NEW.PD1995 <=> OLD.PD1995 || NEW.PD1996 <=> OLD.PD1996 || NEW.PD1997 <=> OLD.PD1997 || NEW.PD1998 <=> OLD.PD1998 || NEW.PD1999 <=> OLD.PD1999 || NEW.PD2000 <=> OLD.PD2000 || NEW.PD2001 <=> OLD.PD2001 || NEW.PD2002 <=> OLD.PD2002 || NEW.PD2003 <=> OLD.PD2003 || NEW.PD2004 <=> OLD.PD2004 || NEW.PD2005 <=> OLD.PD2005 || NEW.PD2006 <=> OLD.PD2006 || NEW.PD2007 <=> OLD.PD2007 || NEW.PD2008 <=> OLD.PD2008 || NEW.PD2009 <=> OLD.PD2009 || NEW.PD2010 <=> OLD.PD2010 || NEW.PD2011 <=> OLD.PD2011 || NEW.PD2012 <=> OLD.PD2012 || NEW.PD2013 <=> OLD.PD2013 || NEW.PD2014 <=> OLD.PD2014 || NEW.PD2015 <=> OLD.PD2015 || NEW.PD2016 <=> OLD.PD2016 || NEW.PD2017 <=> OLD.PD2017 || NEW.PD2018 <=> OLD.PD2018 || NEW.PD2019 <=> OLD.PD2019 || NEW.PD2020 <=> OLD.PD2020 || NEW.PD2021 <=> OLD.PD2021 || NEW.PD2022 <=> OLD.PD2022 || NEW.PD2023 <=> OLD.PD2023 || NEW.PD2024 <=> OLD.PD2024 || NEW.PD2025 <=> OLD.PD2025 || NEW.PD2026 <=> OLD.PD2026 || NEW.PD2027 <=> OLD.PD2027 || NEW.InvoiceSent <=> OLD.InvoiceSent || NEW.Cancellation <=> OLD.Cancellation || NEW.DateTerm <=> OLD.DateTerm || NEW.Alert <=> OLD.Alert THEN

    CASE NEW.Activity

		WHEN 'Active' THEN (Active); 
		
        	WHEN 'Inactive' THEN INSERT INTO `InactiveMembers` (Activity, MType, ClanID, Region, State, ReNewMonth, LastName, FirstName, SpouseName, Prefix, StreetAddress, City, States, Zip, Country, SNBD, TelePhone, EmailAddress, ReNew, Joined, PD2016, PD2017, PD2018, PD2019, PD2020, PD2021, PD2022, PD2023, PD2024, PD2025, PD2026, PD2027, InvoiceSent, Cancellation, DateTerm, Alert) VALUES (NEW.Activity, NEW.MType, NEW.ClanID, NEW.Region, NEW.State, NEW.ReNewMonth, NEW.LastName, NEW.FirstName, NEW.SpouseName, NEW.Prefix, NEW.StreetAddress, NEW.City, NEW.States, NEW.Zip, NEW.Country, NEW.SNBD, NEW.TelePhone, NEW.EmailAddress, NEW.ReNew, NEW.Joined, NEW.PD2016, NEW.PD2017, NEW.PD2018, NEW.PD2019, NEW.PD2020, NEW.PD2021, NEW.PD2022, NEW.PD2023, NEW.PD2024, NEW.PD2025, NEW.PD2026, NEW.PD2027, NEW.InvoiceSent, NEW.Cancellation, NEW.DateTerm, NEW.Alert);     
		
		WHEN 'Resigned' THEN INSERT INTO `ResignedMembers` (Activity, MType, ClanID, Region, State, ReNewMonth, LastName, FirstName, SpouseName, Prefix, StreetAddress, City, States, Zip, Country, SNBD, TelePhone, EmailAddress, ReNew, Joined, PD2016, PD2017, PD2018, PD2019, PD2020, PD2021, PD2022, PD2023, PD2024, PD2025, PD2026, PD2027, InvoiceSent, Cancellation, DateTerm, Alert) VALUES (NEW.Activity, NEW.MType, NEW.ClanID, NEW.Region, NEW.State, NEW.ReNewMonth, NEW.LastName, NEW.FirstName, NEW.SpouseName, NEW.Prefix, NEW.StreetAddress, NEW.City, NEW.States, NEW.Zip, NEW.Country, NEW.SNBD, NEW.TelePhone, NEW.EmailAddress, NEW.ReNew, NEW.Joined, NEW.PD2016, NEW.PD2017, NEW.PD2018, NEW.PD2019, NEW.PD2020, NEW.PD2021, NEW.PD2022, NEW.PD2023, NEW.PD2024, NEW.PD2025, NEW.PD2026, NEW.PD2027, NEW.InvoiceSent, NEW.Cancellation, NEW.DateTerm, NEW.Alert);	
		
		WHEN 'Deceased' THEN INSERT INTO `KnownDeceased` (Activity, MType, ClanID, Region, State, ReNewMonth, LastName, FirstName, SpouseName, Prefix, StreetAddress, City, States, Zip, Country, SNBD, TelePhone, EmailAddress, ReNew, Joined, PD2016, PD2017, PD2018, PD2019, PD2020, PD2021, PD2022, PD2023, PD2024, PD2025, PD2026, PD2027, InvoiceSent, Cancellation, DateTerm, Alert) VALUES (NEW.Activity, NEW.MType, NEW.ClanID, NEW.Region, NEW.State, NEW.ReNewMonth, NEW.LastName, NEW.FirstName, NEW.SpouseName, NEW.Prefix, NEW.StreetAddress, NEW.City, NEW.States, NEW.Zip, NEW.Country, NEW.SNBD, NEW.TelePhone, NEW.EmailAddress, NEW.ReNew, NEW.Joined, NEW.PD2016, NEW.PD2017, NEW.PD2018, NEW.PD2019, NEW.PD2020, NEW.PD2021, NEW.PD2022, NEW.PD2023, NEW.PD2024, NEW.PD2025, NEW.PD2026, NEW.PD2027, NEW.InvoiceSent, NEW.Cancellation, NEW.DateTerm, NEW.Alert);

    END CASE;

  END IF;

END; //

DELIMITER ;
The Other Triggers I have on tables, Inactive and Resigned work like a champ and of course no Trigger in needed for Deceased.
If member stops paying dues and goes "Inactive" and a year or so latter pays dues clicking the "Active" button in the InactiveMembers table moves him or her back to MembershipRoster, this all ties to our Genealogy Database and Membership access, and all validation is by ClanID which is unique and dies with members.

Thanks for your time.
What we envision, we make happen.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 518
Joined: 2018-04-01 10:12

Re: Radio buttons as trigger events in Mysql

Post by pböttcher » 2018-05-07 06:24

Hi Jay,

sorry, still not sure what you try to acheive.
The trigger will always get fired and runs through the logic implemented in the trigger. So I think you do not need your IF statement, as you check if anything changed. Unless you call the update record with the same data that you already had.
Also, can you please replace your "<=>" by "!=".
The New.Activity check in the CASE is not need as you do not want any changes. If you want a default action if none of the case checks matches you can add an ELSE statement at the end of the CASE check.

regards
Pascal

User avatar
Jay Webb
Veteran Member
Posts: 55
Joined: 2017-08-26 15:27
Contact:

Re: Radio buttons as trigger events in Mysql

Post by Jay Webb » 2018-05-07 08:23

Hello Pascal

Big thank you!
I changed "<=>" to "!=" and that did it, everything works, I can now make a change on any of the 4 table and Update and it's not firing the trigger, but if one of the Activity buttons is checked and Updated it fires correctly and I feel confident I can manually delete the row After Update. I can tell you this will make this project possible for future admins and all the membership.

With much gratitude.
Jay
What we envision, we make happen.

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 518
Joined: 2018-04-01 10:12

Re: Radio buttons as trigger events in Mysql

Post by pböttcher » 2018-05-07 08:54

good to see that it is working fine :-)

Post Reply