0

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

USE infrastructure$$

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?

2

You need a before insert trigger ("after insert", the bird has flown, it's useless modifying the new vales - if MySQL allows you). In the trigger body, use the following code:

Declare v_tax int;
select tr.tax_combined_rate into v_tax from sales_tax_rate as tr
  where NEW.res_zip = tr.zip_code;
set NEW.res_sales_tax = v_tax;

See the effect here: https://www.db-fiddle.com/f/eEiK6pKGEQEoYL9BPdfooP/0

0

Community wiki answer based on comments left by the question author:

I did as suggested and inserted records from a csv file and then from a command prompt, the sales tax was 0 and the table at a rate of .0925

It turned out V_tax was defined as an int, the tax data type was decimal(8,6). I changed to:

declare v_tax decimal(8,6)

...and it works.

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.