{"id":412,"date":"2009-02-02T13:23:38","date_gmt":"2009-02-02T11:23:38","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=412"},"modified":"2009-02-02T13:26:55","modified_gmt":"2009-02-02T11:26:55","slug":"triggers-use-case-compilation-part-iii","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/triggers-use-case-compilation-part-iii","title":{"rendered":"Triggers Use Case Compilation, Part III"},"content":{"rendered":"

The previous two parts have looked at some solutions offered by triggers. Let’s look now at some wishful triggers solutions, which are currently unavailable because of triggers limitations.<\/p>\n

Triggers Use Case Compilation, Part I<\/a><\/p>\n

Triggers Use Case Compilation, Part II<\/a><\/p>\n

Limitations and wishful features<\/h4>\n

Triggers are slow<\/p>\n

The overhead of adding triggers is usually an even breaker. But I would like to believe speed will improve in time!<\/p>\n

Triggers cannot act on the same table which activated them.<\/p>\n

A thing I would like to do is have a rotating table. A log table is a perfect example: I only want to store logs up to 7 days back, or up to 1M rows. ON INSERT, (or once every 1000 inserts or so), I wish to remove oldest rows. This is not possible today since I can’t DELETE rows from the same table which caused the ON INSERT trigger to run. It can’t be hacked by calling on another table, then doing a circular trigger trick. MySQL will raise an error on run time, complaining about a loop.<\/p>\n

Triggers cannot act on system tables.<\/p>\n

Now why would I want to do that? Well, one of the first things I look at when reviewing a database is the users grants. I always<\/em> find a list of users which is just too permissive, with far too many users than required. I once came upon a database with 273 users, where only 5 of them were actually in use. “When were these added?”, I asked – but nobody knew.<\/p>\n

I would love to have an ON INSERT and an ON UPDATE trigger on the mysql.user table, which lists down the time of user creation and the invoking user (who would usually be ‘root’) and host, so it’s easier to track down who did what.<\/p>\n

You cannot execute prepared statements from within a trigger.<\/p>\n

Not much to add here. The possibilities are too many.<\/p>\n

You can’t spawn an ANALYZE TABLE from a trigger<\/p>\n

What I want to do is to run an ANALYZE TABLE once every 10K inserts or deletes, so the table takes care of itself. I’ve tried hacking this with prepared statements (you can’t use them); with cursors (you can only run a cursor on SELECT queries) or otherwise SQL hacks (none worked). If anyone finds a hack around it – please let me know!<\/p>\n

You can’t have more than one trigger on the same event per table<\/p>\n

This is more of a design issue. If I want to have two things BEFORE INSERT on City, I need to code both in the same trigger. This means adding functionality involves editing existing, tested, working code. It would be much nicer if two such triggers could play along.<\/p>\n

A dirty workaround to problematic issues<\/h4>\n

There is a dirty workaround to some issues.<\/p>\n

Take, for example, the rotating tables problem. Instead of the trigger executing the following query:<\/p>\n

\n
DELETE FROM logs WHERE time < DATE_ADD(NOW(), INTERVAL -7 DAY)<\/pre>\n<\/blockquote>\n
(as we’ve already noted was impossible), the triggers can write down the query as TEXT into some queries_to_run<\/strong> table. A cronjob can periodically check this table and execute whatever is in it, removing executed rows.<\/p>\n
MySQL 5.1’s event scheduler can also be used for such statements which are invokable (like said DELETE).<\/p>\n","protected":false},"excerpt":{"rendered":"
The previous two parts have looked at some solutions offered by triggers. Let’s look now at some wishful triggers solutions, which are currently unavailable because of triggers limitations. Triggers Use Case Compilation, Part I Triggers Use Case Compilation, Part II Limitations and wishful features Triggers are slow The overhead of adding triggers is usually an […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[33,30],"class_list":["post-412","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-event-scheduler","tag-triggers"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-6E","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/412","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=412"}],"version-history":[{"count":10,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/412\/revisions"}],"predecessor-version":[{"id":538,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/412\/revisions\/538"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=412"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=412"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=412"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}