MySQL - Multi-Row Trigger
MySQL - Multi-Row Trigger
I am looking for a way in MySQL to validate input with a trigger on more than one row at one time. More specifically, I'd want to submit an update statement that updates 2+ rows (whereas each individual update would fire the trigger, but the entire update statement would not).
delimiter //
CREATE TRIGGER checkdistsum
AFTER UPDATE ON tests_detail
for each row
BEGIN
SET @totaldist = (select count(*) from (select sum(td.DISTRIBUTION_PCT) as
total
from models m,.tests t,tests_detail td
where m.model_id = td.MODEL_ID and t.TEST_ID = td.TEST_ID
group by t.test_id having total != 1) as totalquery);
IF @totaldist > 0 THEN signal sqlstate '45000';
END IF;
END;//
The trigger will fire value of the query does not equal 1. But I want that to only fire after the entire update statement gets executed.
An example of an update statement would be:
UPDATE tests_detail
SET DISTRIBUTION_PCT = (CASE
WHEN MODEL_ID = 3 THEN .40
WHEN MODEL_ID = 2 THEN .60
ELSE DISTRIBUTION_PCT
END)
WHERE MODEL_ID IN(2,3);
In this case - the sum of the update statement would be 1. However, if only one row gets updated at a time, the trigger would fire prematurely.
Thanks! It doesn't have to be a trigger. The concept would be that I'd run a check on a multi row update before the transaction gets committed to the database. In the case I am looking for - I want to ensure all the rows that are updated sum up to 1.
– terp26
Sep 17 '18 at 2:42
before or after the update? Show the quer(ies)/constraints? It might be possible to wrap this in a transaction.
– danblack
Sep 17 '18 at 2:55
@danblack I made some edits on the question with some code examples. Thanks.
– terp26
Sep 17 '18 at 3:17
0
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy
MySQL triggers always fire once for each row affected. It's not clear what you're trying to accomplish, so I can't suggest an alternative.
– Bill Karwin
Sep 17 '18 at 2:26