tag:blogger.com,1999:blog-15319370.post6760649886277364297..comments2024-03-05T11:16:00.846+01:00Comments on Roland Bouman's blog: MySQL Hacks: Preventing deletion of specific rowsrpboumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comBlogger13125tag:blogger.com,1999:blog-15319370.post-24493410786024378432020-04-27T18:02:57.772+02:002020-04-27T18:02:57.772+02:00> @Madhivanan how can I prevent deletion from a...> @Madhivanan how can I prevent deletion from a view? Do you have any idea?<br /><br />Why not simply use the GRANT/privileges system for that? No hacking needed. rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-45082099269908040482020-04-27T17:52:04.123+02:002020-04-27T17:52:04.123+02:00@Madhivanan how can I prevent deletion from a view...@Madhivanan how can I prevent deletion from a view? Do you have any idea?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-30119081120578367522013-08-28T16:04:11.200+02:002013-08-28T16:04:11.200+02:00Xevi, I haven't. Looks like a bug. Goto bugs.m...Xevi, I haven't. Looks like a bug. Goto bugs.mysql.com, and file it.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-48058027414991109822013-08-28T15:49:43.940+02:002013-08-28T15:49:43.940+02:00Have you seen this?
http://forums.mysql.com/read.p...Have you seen this?<br />http://forums.mysql.com/read.php?105,593422,593422#msg-593422<br /><br />I've discovered by chance this morning.Xevihttps://www.blogger.com/profile/16630450046058452556noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-75904692339524090412013-08-17T16:35:32.721+02:002013-08-17T16:35:32.721+02:00You're terribly rightYou're terribly rightXevihttps://www.blogger.com/profile/16630450046058452556noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-21806629329020185782013-08-15T13:42:40.669+02:002013-08-15T13:42:40.669+02:00Hi Xevi,
ok - I see what you mean. Instead of ha...Hi Xevi, <br /><br />ok - I see what you mean. Instead of having a separate guard table, you add a nullable "guard column" to the table itself, which has a foreign key constraint pointing to the primary key of the table itself. The rows that maybe freely deleted have a NULL in the guard column, the rows that musn't be deleted reference themselves, correct?<br /><br />I think it's a creative solution. Of course you'd need to remove write privileges to the guard column from unprivileged users.<br /><br />You should however be vary careful to use this solution outside MySQL or actually InnoDB. InnoDB will prevent deletion of a self-referencing row, but for example Oracle will not (correct me if I'm wrong). As long as the row only references itself, it can be deleted without ever violating referential integrity (since there are no orphans if the row would simple be deleted). rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-41794152931768701632013-08-15T13:03:00.854+02:002013-08-15T13:03:00.854+02:00Why not go further, and do it even more tricky?
Yo...Why not go further, and do it even more tricky?<br />Your idea is fucking cool, but you do not really need two tables. One is enough.<br /><br /><a href="https://snipt.net/temple/self-protect-read-messages/" rel="nofollow">Here's a snippet with a little gear more</a><br />Hope you like it.Xevihttps://www.blogger.com/profile/16630450046058452556noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-26768041247795390572012-08-24T12:53:04.840+02:002012-08-24T12:53:04.840+02:00@Madhivanan, yeah, that's a good point, and th...@Madhivanan, yeah, that's a good point, and this is probably what I'd do if I had such a requirement.<br /><br />I guess I was primed by the exact problem statement, and the OP reporting that they couldn't get it to work with a trigger made me explore if I could figure out the underlying problem behind that, rather than providing a functionally equivalent (and probably superior) solution.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-89116413782497515342012-08-24T12:39:15.769+02:002012-08-24T12:39:15.769+02:00How about creating a view that excludes data of de...How about creating a view that excludes data of deptid=10 and give users access to only this view and not the underlying table? This way you don't need additional logics implementedMadhivananhttp://beyondrelational.com/blogs/madhivanannoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-50263759041895146512012-03-08T19:37:41.586+01:002012-03-08T19:37:41.586+01:00@Unknown: I agree. I just enjoy finding a hack :)@Unknown: I agree. I just enjoy finding a hack :)rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-61064182620400007012012-03-08T19:21:05.974+01:002012-03-08T19:21:05.974+01:00I admit that this is a novel solution to the users...I admit that this is a novel solution to the users particular problem, but where I have a fault with it is the fact the situation even came up. If a user or program is able to do a delete all like that, then already the DBA gave away too much power, and something like that might be better handled through a stored procedure.Unknownhttps://www.blogger.com/profile/11339576196748434564noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-55629547681922788442011-10-21T15:58:33.036+02:002011-10-21T15:58:33.036+02:00Hi Marc,
well, the original requirement was that ...Hi Marc,<br /><br />well, the original requirement was that a DELETE that would include the rows that are to be retained should not fail, rather it should "simply" remove only the other rows. Like I pointed out in my initial reply: <br /><br />"Raising the error won't help you achieve your goal: as soon as you raise the error, the statement will either abort (in case of a non-transactional table) or rollback all row changes made up to raising the error (in case of a transactional table)"<br /><br />Of course, if the requirement would be changed to allow failure of the entire statement in case a row that matches the condition would have been deleted, and if one would decide to implement this with a trigger on MySQL 5.5 or higher, then I would most definitely recommend using SIGNAL to raise a custom error rather than some hack.<br /><br />If we would accept a change of requirements then I am not sure what I'd choose - the solution with the trigger or with the foreign key. Although a bit quirky, I feel the foreign key has a few things going for it. It's quite flexible, as we only need to add or delete guard rows as needed. I also assume it delivers better performance, I really should check that some time.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-40224797003993457762011-10-21T15:49:50.296+02:002011-10-21T15:49:50.296+02:00Hi Roland.
Have you considered a SIGNAL statement...Hi Roland.<br /><br />Have you considered a SIGNAL statement inside a BEFORE DELETE trigger ?<br /><br />Regards,<br />-- MarcMarc Alffhttps://www.blogger.com/profile/10657419226323022344noreply@blogger.com