[MDEV-10912] CREATE OR REPLACE TRIGGER produces ER_TRG_DOES_NOT_EXIST Created: 2016-09-27  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Triggers
Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.11

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-7286 TRIGGER: CREATE OR REPLACE, CREATE IF... Closed

 Description    

create table t1 (i int);
 
create table t2 (i int);
 
 
 
--echo # This works OK
 
create or replace trigger tr after insert on t1 for each row set @a=1;
 
--echo # This also works OK
 
create or replace trigger tr after insert on t1 for each row set @a=1;
 
--echo # This fails
 
create or replace trigger tr after insert on t2 for each row set @a=1;
 
 
 
drop table t1, t2;


MariaDB [test]> create or replace trigger tr after insert on t2 for each row set @a=1;
 
ERROR 1360 (HY000): Trigger does not exist



 Comments   
Comment by Michael Widenius [ 2021-05-10 ]

I talked with Sergei and we agreed that create or replace should work even if the original trigger was for another table.

This is best to be done in 10.6 where we can use the atomic ddl code to make this atomic.

In case of 'create or replace trigger' for a trigger that was originally for another table,
the code should work as follows:

  • Take a ddl lock for the trigger name (Already done in 10.5 as part of MDEV-25606).
  • Execute 'drop trigger' for the original trigger. However, do not close the ddl log.
  • Execute create trigger for the new table.
  • If something goes wrong, use the ddl_log_revert() to restore the original trigger.
Generated at Thu Feb 08 07:45:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.