![]() ![]() To also include counts of those rows, the BEFORE UPDATE/BEFORE DELETE trigger on the parent table would need to perform a query to find the number of rows that would be affected in the child tables. It's important to note that triggers are not fired for rows affected through FOREIGN KEY constraint actions. This approach would also require a preliminary statement to reset the user-defined variable to zero, before each INSERT, UPDATE or DELETE statement is issued, e.g. Throwing an error would cause the operation to be cancelled. and need compare the cumulative counter to a specified limit (coded into the trigger), and then SIGNAL (or otherwise throw an error) when the specified limit is reached or exceeded. ![]() The trigger would need to increment a counter in a user-defined variable (e.g. These are extra steps that need to be performed apart from the actual DML statement.Īnother possibility, to effect this type of restriction would be to implement FOR EACH ROW TRIGGERs on specific tables that track the number of times the trigger is fired. This approach doesn't provide any restriction or prohibition on a DML statement itself. And then check the returned count(s) to determine whether an UPDATE or DELETE should be issued. One possibility is to run a separate "precheck" query, using the same criteria as the UPDATE or DELETE statement to be issued, and gather counts of rows that would be affected. There are a couple of approaches we could take to implement this kind of restriction, but all of those approaches will require some additional SQL statements and logic. There's no functionality builtin to MySQL that achieves this prohibition, automatically cancelling a DML statement (INSERT, UPDATE or DELETE statement) that affects more than N rows.Īnd there's not really a way to implement this kind of restriction for any and all possible INSERT, UPDATE or DELETE statements that could be executed.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |