tag:blogger.com,1999:blog-3080615211468083537.post4514184142882218400..comments2023-09-27T11:36:08.392+03:00Comments on Blog of (former?) MySQL Entomologist: How to Drop the TriggerValerii Kravchukhttp://www.blogger.com/profile/13158916419325454260noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-3080615211468083537.post-83227126522211911052013-05-11T00:11:08.692+03:002013-05-11T00:11:08.692+03:00Thank you! I follow your steps, and after changed ...Thank you! I follow your steps, and after changed wrong privilege *TRG file to mode 660, I can drop triggers. bengbenghttps://www.blogger.com/profile/18189037960754325260noreply@blogger.comtag:blogger.com,1999:blog-3080615211468083537.post-33499482185815971302013-02-03T18:45:29.829+02:002013-02-03T18:45:29.829+02:00Yes, I truly hope that real data dictionary in MyS...Yes, I truly hope that real data dictionary in MySQL 5.7 will solve this (and other related problems, like I_S queries being slow) once and forever.Valerii Kravchukhttps://www.blogger.com/profile/13158916419325454260noreply@blogger.comtag:blogger.com,1999:blog-3080615211468083537.post-71807007361672079382013-02-02T15:32:34.137+02:002013-02-02T15:32:34.137+02:00Or if you could say "drop trigger trg1 on dat...Or if you could say "drop trigger trg1 on database.table" then mysql would know exactly which TRG file to edit (without scanning all) and it could cleanup better.sbesterhttps://www.blogger.com/profile/01382626013503952498noreply@blogger.comtag:blogger.com,1999:blog-3080615211468083537.post-40448302261430182312013-02-02T15:29:00.052+02:002013-02-02T15:29:00.052+02:00I wish there was a "create or replace trigger...I wish there was a "create or replace trigger" syntax to fix this half-dropped/half-created triggers. but i think i'll just wait for real data dictionary instead ;-0sbesterhttps://www.blogger.com/profile/01382626013503952498noreply@blogger.comtag:blogger.com,1999:blog-3080615211468083537.post-31193003683270207462013-02-01T19:05:30.265+02:002013-02-01T19:05:30.265+02:00I've got a question on Facebook on what will h...I've got a question on Facebook on what will happen if trigger with the same name is added when .TRN file is missing. Let me repeat my reply here:<br /><br />Quick test show that, even with .TRG file in place, trigger is added:<br /><br />mysql> show triggers\G<br />*************************** 1. row ***************************<br />Trigger: tr1<br />Event: INSERT<br />Table: t1<br />Statement: set @a=1<br />Timing: BEFORE<br />Created: NULL<br />sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION<br />Definer: root@localhost<br />character_set_client: utf8<br />collation_connection: utf8_general_ci<br />Database Collation: utf8_general_ci<br />1 row in set (0.01 sec)<br /><br />mysql> show create trigger tr1\G<br />ERROR 1360 (HY000): Trigger does not exist<br /><br />The above also does not work as you can see.<br /><br />mysql> drop trigger tr1;<br />ERROR 1360 (HY000): Trigger does not exist<br /><br />Now,. let's try to create another one with the same name. Internals manual explained that .TRN file is used exactly to prevent this, so trigger is created:<br /><br />mysql> create trigger tr1 after insert on t1 for each row set @b=2;<br />Query OK, 0 rows affected (0.09 sec)<br /><br />mysql> show triggers\G<br />*************************** 1. row ***************************<br />Trigger: tr1<br />Event: INSERT<br />Table: t1<br />Statement: set @a=1<br />Timing: BEFORE<br />Created: NULL<br />sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION<br />Definer: root@localhost<br />character_set_client: utf8<br />collation_connection: utf8_general_ci<br />Database Collation: utf8_general_ci<br />*************************** 2. row ***************************<br />Trigger: tr1<br />Event: INSERT<br />Table: t1<br />Statement: set @b=2<br />Timing: AFTER<br />Created: NULL<br />sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION<br />Definer: root@localhost<br />character_set_client: utf8<br />collation_connection: utf8_general_ci<br />Database Collation: utf8_general_ci<br />2 rows in set (0.02 sec)<br /><br />So, we have 2 triggers with the same name. Now, trying to delete:<br /><br />mysql> drop trigger tr1;<br />Query OK, 0 rows affected (0.05 sec)<br /><br />Wow, it works!<br /><br />mysql> show triggers\G<br />*************************** 1. row ***************************<br />Trigger: tr1<br />Event: INSERT<br />Table: t1<br />Statement: set @b=2<br />Timing: AFTER<br />Created: NULL<br />sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION<br />Definer: root@localhost<br />character_set_client: utf8<br />collation_connection: utf8_general_ci<br />Database Collation: utf8_general_ci<br />1 row in set (0.02 sec)<br /><br />But only once:<br /><br />mysql> drop trigger tr1;<br />ERROR 1360 (HY000): Trigger does not exist<br /><br />This is crazy, and probably real fix will be only in 5.7 with real data dictionary Valerii Kravchukhttps://www.blogger.com/profile/13158916419325454260noreply@blogger.comtag:blogger.com,1999:blog-3080615211468083537.post-53603713509111331822013-02-01T19:01:45.982+02:002013-02-01T19:01:45.982+02:00Yes, trigger will still work. Look:
mysql> cre...Yes, trigger will still work. Look:<br /><br />mysql> create trigger tr1 before insert on t1 for each row set @a=1;<br />Query OK, 0 rows affected (0.09 sec)<br /><br />mysql> show triggers\G<br />*************************** 1. row ***************************<br /> Trigger: tr1<br /> Event: INSERT<br /> Table: t1<br /> Statement: set @a=1<br /> Timing: BEFORE<br /> Created: NULL<br /> sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION<br /> Definer: root@localhost<br />character_set_client: utf8<br />collation_connection: utf8_general_ci<br /> Database Collation: utf8_general_ci<br />1 row in set (0.02 sec)<br /><br />mysql> select @a;<br />+------+<br />| @a |<br />+------+<br />| NULL |<br />+------+<br />1 row in set (0.03 sec)<br /><br />mysql> show create trigger tr1\G<br />ERROR 1360 (HY000): Trigger does not exist<br />mysql> insert into t1 values(1,1);<br />Query OK, 1 row affected (0.23 sec)<br /><br />mysql> select @a;<br />+------+<br />| @a |<br />+------+<br />| 1 |<br />+------+<br />1 row in set (0.00 sec)<br /><br />I've removed .TRN file immediately after creating trigger and, as you can see from @a user variable value, it worked when INSERT happened. Trigger's code is in t1.TRG, so association is there.Valerii Kravchukhttps://www.blogger.com/profile/13158916419325454260noreply@blogger.comtag:blogger.com,1999:blog-3080615211468083537.post-39091319205926356282013-02-01T15:14:14.371+02:002013-02-01T15:14:14.371+02:00Hi Valeriy,
Thanks for the post, I have question,...Hi Valeriy,<br /><br />Thanks for the post, I have question, would a trigger work if it doesnt have a .TRN file.<br /><br />I believe that .TRN stores a object dependence info for the trigger, without it the trigger should not work.<br /><br />Thanks,<br />Akshay SAkshay Suryavanshihttps://www.blogger.com/profile/11486438095809406675noreply@blogger.com