1

I have a Stored Procedure written which inserts values from Table 1 to Table 2. Now, a trigger (AFTER INSERT) is created on Table 1 and is made to call the above Stored Procedure. The above scenario is working fine. But what I wanted to achieve is that when one row from T1 is getting inserted into T2 then I want the same row to be deleted from T1. I tried using delete statement but MySQL doesn't allow the same.

Here is my current trigger:

DELIMITER $$
CREATE TRIGGER `ins` AFTER INSERT ON `T1`
FOR EACH ROW
BEGIN
   CALL sp();   
END;
$$

In SP I have the line Delete from T1 where ID = sb; (This is creating problem) In T1 there is a ID field as PK ans in T2 there is an RID field both are used for referencing purpose.

mkj
  • 2,761
  • 5
  • 24
  • 28
Srijit B
  • 59
  • 1
  • 4
  • 16
  • 1
    You have to spoof it with a delayed delete (have a column in that table to set a value). During the insert trigger for `T1` you perform the setting of that column to some value. Have an Event clean things up. See these I wrote for events, [one](http://stackoverflow.com/a/37901661) and [two](http://stackoverflow.com/a/32508935). Events are essentially built in cron that fires stored procedure like stored programs on the schedule (interval) of your choice. Also note that many people just do Soft Deletes. Like what stackoverflow does when you delete a post. It is not deleted from the db. Flag set – Drew Oct 17 '16 at 04:10
  • Even if I want to do manual delete I need something (some flag value) to be set in T1 which I can delete later. Update operation is also now allowed in insert trigger. – Srijit B Oct 17 '16 at 09:55
  • Sure it is. You just need to know how to do it – Drew Oct 17 '16 at 15:24

0 Answers0