If you open schema.rb after the migration of the SQL trigger, you would notice that there’s no information about the trigger function in it. rescue ActiveRecord :: StatementInvalid => e flash = "Items total exceeded budget allocation, please try again" render 'edit' end Adding trigger to schema.rb using fx gem The above statement will select the “allocation_cents” value from the budget which the new item row belongs to, and save it into the variable allowed_total. SELECT INTO allowed_total allocation_cents
The “ NEW” in the function refers to the new row we want to insert into the items table, or the existing row we want to update in the items table. The actual function is located between the BEGIN and END statement. In the DECLARE section, we can declare two variables allowed_total and new_total, they both have the type BIGINT. The function accepts no parameter and return a trigger type (RETURNS TRIGGER), you can think of trigger like ActiveRecord callback, which we can set it to execute after a model object (row) is created (inserted).īetween AS $func$ and $func$ is the actual SQL function, we can think of AS $func$ and $func$ as the delimiter for multiline strings in Ruby, similar like the «-SQL and SQL part. RAISE EXCEPTION 'Items total price is larger than budget allocation ',įOR EACH ROW EXECUTE PROCEDURE check_item_total() ĭROP TRIGGER item_total_trigger ON items ĬREATE_AND_AND_REPLACE_FUNCTION will create a function with the name check_item_total() or replace it if it already exists.
We can simulate this concurrent add items by multiple users scenario, by creating multiple threads and join them to run multple add items statement at the same time :Ĭlass CreateTriggerItemTotalCheck allowed_total
User 3 check if the items total price is larger than budget allocation, validation passes.User 2 check if the items total price is larger than budget allocation, validation passes.User 1 check if the items total price is larger than budget allocation, validation passes.When multiple users add item to the budget at (almost) the same time, the model validation passes as the validations are done at the almost same time, which the items of every user is not added in yet. This validation works well… until you have multiple users adding items to the budget at almost the same time. add ( :base, 'Items total price has exceeded budget allocation' ) end end endįor example, if we have a budget of 10000 cents, and we keep adding an item that cost 3000 cents into the budget, it would raise an error on the 4th item (4 x 3000 = 12000, which is larger than 10000).