Change table from your trigger in Mysql-PhpMyAdmin
I am using Mysql in phpMyAdmin where I need to delete a record from table A if I insert a row with the same primary key. I thought to do this in trigger tableA BEFORE INSERT For Ex if table A contains
1 Hai Hello
here 1 is the main key
And now if I insert a row 1 Bye Hello
, then the BEFORE INSERT trigger will delete the old record and then add a new row (2). But Mysql has a limitation that it cannot update a table inside a trigger defined for the same table.
It gives error
# 1442 - Cannot update table 'tableA' in stored function / trigger because it is already in use by the expression that calls this stored function / trigger.
So, I changed my path, I called the procedure from the trigger BEFORE INSERT tableA, and in this procedure I am doing the task I thought I would do in the trigger. But unfortunately I am getting the same error. In the trigger BEFORE INSERT I just named the procedure as
CALL proce1(new.Reg_No);
In the procedure I did this
DECLARE toup integer;
select count(*) into toup from tableA where Reg_No=reg;/*Here Reg_No is primary key */
if toup > 0 then
delete from tableA where Reg_No=reg;
end if;
It takes a different idea to achieve this. Help me.....
source to share
I don't like to use triggers so much because they are difficult to control. They will also degrade performance. I don't mind triggers as they can be handy in some cases.
In your case, if you use REPLACE(....)
or INSERT INTO .... ON DUPLICATE KEY UPDATE
or evenINSERT INTO IGNORE .....
REPLACE(....)
will delete a record if a record is found and insert a new one with the same id.
INSERT INTO .... ON DUPLICATE KEY UPDATE
will allow you to override an existing field if a duplicate is found.
INSERT INTO IGNORE .....
will allow you to ignore the new inserted line if it already exists
Since you mentioned in the comments that you are importing records from a file, try using LOAD DATA INFILE which will allow you the REPLACE
field in a duplicate
LOAD DATA LOCAL INFILE 'x3export.txt'
REPLACE INTO TABLE x3export
source to share