Am using MYSQL Workbench
I have a residence table with a sales tax rate and when a new residence record is inserted I want its sales_tax field updated from a tax rate_table. the 2 tables are connected by zip_code.
I am using WORKBENCH Table Triggers to create the code
I have tried several things but I get errors either on syntax or during a load
If I use "UPDATE" I get an error code 1442 which seems to indicate that "UPDATE" is not allowed.
Things I have tried, code generated by workbench
CREATE DEFINER = CURRENT_USER TRIGGER `infrastructure`.`Residence_AFTER_INSERT` AFTER INSERT ON `Residence` FOR EACH ROW BEGIN select tr.tax_combined_rate as tax from sales_tax_rate as tr where residence.res_zip = tr.zip_code; set residence.res_sales_tax = tax; END
Result ERROR: Error 1193: Unknown system variable 'res_sales_tax'
I then comment out the line “set residence.res_sales_tax = tax
Result ERROR: Error 1415: Not allowed to return a result set from a trigger
This error suggests that I can not get a value from another table from within a trigger
Can some one give me some guidance Can this be done?