After Insert Trigger

[Ketaki Pandya]

Hello,

I wrote an After insert trigger. But since I am trying to delete the records in the same trigger code from the same table on which the trigger is created so it was giving error. I found on google that I can use Ignore keyword with Delete to take care of the error. Now the error is gone but Delete statement is not deleting old records from the table. Please advise.

USE TEST;
-- SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME = 'TR_ORDERS_INS'
DROP TRIGGER IF EXISTS TR_ORDERS_INS;

DELIMITER $$
CREATE TRIGGER TR_ORDERS_INS
AFTER INSERT ON TEST.orderdetail for each row
BEGIN
DECLARE MAX_DATE1 DATETIME;
IF EXISTS
(
select 1 from TEST.orderdetail O
where O.STOREID = NEW.STOREID
AND O.ITEMID = NEW.ITEMID
AND O.ORDERDATE = NEW.ORDERDATE
)
THEN

SET MAX_DATE1 = (SELECT max(DateTimeStamp)
from TEST.orderdetail
where itemid = NEW.itemid
and storeid = NEW.storeid
and OrderDate = NEW.orderdate);


INSERT INTO TEST.orders_history
(ORDERID,STOREID,ITEMID,ORDERDATE,QUANTITY,BONUSBUCK,FLATBONUSBUCK,PRICE,STOREPRICE,PRICEOVERRIDE,
PRICEOVERRIDEFLAG,DIRECTSHIPFLAG,FREEITEMFLAG,SRP,DATETIMESTAMP,RECORDNUMBER)
SELECT ORDERID,STOREID,ITEMID,ORDERDATE,QUANTITY,BONUSBUCK,FLATBONUSBUCK,PRICE,STOREPRICE,PRICEOVERRIDE,
PRICEOVERRIDEFLAG,DIRECTSHIPFLAG,FREEITEMFLAG,SRP,DATETIMESTAMP,RECORDNUMBER
FROM TEST.orderdetail OD
WHERE OD.storeid = NEW.storeid
and OD.itemid = NEW.itemid
and OD.orderdate = NEW.orderdate
and OD.DateTimeStamp < MAX_DATE1;



DELETE IGNORE FROM TEST.test_orderdetail
WHERE storeid = NEW.storeid
and itemid = NEW.itemid
and orderdate = NEW.orderdate
and DateTimeStamp < MAX_DATE1;

END IF;

END$$