Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-31163

In some scenarios triggers limitations may lead to cascade data posioning.




      MariaDB inherited from MySQL, one particularly unpleasant feature, namely "triggers can not access table, on which behalf they were invoked", in some situations, it is not just unpleasant constraint but dangerous limitation. How? I will describe below:
      Lets assume that adding new record to the table, requires also automatic addition of another record to the same table (& similarly deletion of a record, will require deletion of an extra one), such operation should be executed upon data insertion to (or deletion from) the table, so the trigger seems to be ideal solution for such occasion, however due to limitation, it can not be done this way. Situation gets even worse, when addition of new record or modification of existing one, requires checking of other records & if necessary, update some content in the same table, as before that should happened automatically on trigger level & again, it can not be done this way. Of course, the easiest solution to this problem is to create stored procedure, shift logic to its level & somewhat encourage user to use it, under such conditions, this is the only way to ensure proper data handling, unfortunately that does not solve a problem entirely, especially when someone manually (incidentally or not) performs insert, update or delete commands (which can not be blocked completely) upon table, which in such scenario will ensure data poisoning!
      Triggers can access & modify freely all other tables, except that one, on which behalf they were invoked, that is serious limitation of their functionality. It is understandable that recursion will arise, while manipulating same table, in such fashion & if it is handled improperly, it may ultimately lead to endless cycle, but that can be tracked or solved differently.
      Solution to this problem is either to distinguish "after triggers" from "before triggers" & at least give one of them an ability to access table or even better, to create 2nd level of "table events". Such events will act somewhat as "man in the middle", between user & trigger level. They should be fired once, for each insert/update/delete call from user level, be able to both check user request & entire table contents, in such solution triggers will be acting below them. This is nothing else than stored subroutine, the only difference is, it will be called automatically upon table access.


        Issue Links



              Unassigned Unassigned
              v3ctor Jan
              0 Vote for this issue
              2 Start watching this issue



                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.