'MySQL trigger locks table [closed]
I have created trigger in MySQL to update data but problem is as soon as I deploy trigger, it's locking tables and applications using these tables are not able to access.
Edit
Trigger I have created will insert data in a table
USE app ; //db user n database1
DELIMITER $
Create trigger Insert_Data After insert on clients for each row
Begin
insert into database2.customers`
values('1', NULL, NULL, NULL, 'US', 'USD', NULL, NULL, NULL, NULL, NULL, 'America/New_York', NULL, '0', '0', NULL
);
End $
DELIMITER ;
Once above trigger deployed, clients table is not accessible by a website/GUI that inserts data from a submit option on UI page.
Edit
I am adding the SQLError logs, Also apologise for not mentioning I am inserting data in another database using the trigger in one database.
production.ERROR: [stacktrace] 2022-02-25 05:54:43 SQLSTATE[42000]: Syntax error or access violation: 1142 INSERT command denied to user XXXXX
What can I do for this case where trigger should add record in database2 when executed from database1?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|