MySQL Delete on Trigger in same table

Question

Can I delete rows in a table when calling a trigger on it?

such as:

insert into table1 (col1, col2, col3) values (val1, val2, val3);

CREATE DEFINER=`root`@`localhost` TRIGGER `db`.`trig_name` BEFORE INSERT ON `table1` FOR EACH ROW
BEGIN
if (new.val1 > (select val1 from table1 where val2 = new.val2 limit 1))
    delete from table1 where val2 = new.val2;
end if;
END

This is so that I only keep the maximum val1 per val2.

I have done this in PostgreSQL but I cannot find a way to accomplish in MySQL.

You categorically cannot do this in MySQL, because MySQL does not allow recursive triggers. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 12, 2017 at 15:18
Possible duplicate of My MySQL trigger doesn't work, simple syntax, not complicated — e4c5
– e4c5, Commented Jan 12, 2017 at 15:27

Solarflare · Accepted Answer · 2017-01-12 16:03:40Z

1

No, MySQL does not allow this, see Restrictions on Stored Programs

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

If col2 is non-null, and you want to keep only one row (and not several rows that might have the same maximum value), you can achieve the same result by using a unique index on col2, e.g. making it your primary key, and then use

insert into table1 (col1, col2, col3)
values (val1, val2, val3)
on duplicate key update 
   col3 = case when col1 >= values(col1) then col3 else values(col3) end,
   col1 = greatest(values(col1), col1);

to insert your values (and no trigger).

This will keep at most one row per col2, and updating it with the new value if the new row has a higher value for col1. If col1 can be null, use coalesce in the comparisons. (If col2 can be null, this method will not work, because it would not violate the unique constraint.)

answered Jan 12, 2017 at 16:03

Solarflare

11.2k2 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sandeep Over a year ago

can I use a trigger to delete row of the same table on which the trigger is created in sql developer ? The idea is to create a delete record column with True false values. #sqldeveloper

Solarflare Over a year ago

@Sandeep In MySQL (just to make sure, since you mention sql developer), you cannot do any changes to any row in the same table except the currently inserted/updated/deleted single row, and you also cannot change e.g. an insert into a delete or something. Not exactly sure what you mean by "The idea is to create a delete record column", but if you want to insert a row that tells the trigger to delete another row in the same table, that's not possible. (You may want to ask a new question describing your idea/task though, there might be alternatives.)

Sandeep Over a year ago

thank you. My ideas is to delete the current updated/ inserted row where deleterow column = 'True'. say I updated a row and changed its delete row column value to 'True'. I want this row to be automatically deleted by trigger. How can I do that.

Solarflare Over a year ago

@Sandeep You cannot do that with MySQL. Nevertheless, please add a new question for this (and/or check if it already exists), the comment section is not the correct place place to ask a different question (your problem is a bit different from what the answer or the original question is about, and there might be other ways to solve your problem).

Sandeep Over a year ago

yes I will create a new question for this problem. But I think you have answered my query. My apologies for using this comment section as a way to get my answer. Thanks a lot.

Collectives™ on Stack Overflow

MySQL Delete on Trigger in same table

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related