MySQL Forums
Forum List  »  Triggers

After Insert Trigger
Posted by: Ketaki Pandya
Date: June 21, 2019 10:40AM

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$$

Options: ReplyQuote


Subject
Views
Written By
Posted
After Insert Trigger
1120
June 21, 2019 10:40AM
540
June 21, 2019 10:54AM
548
June 21, 2019 11:07AM
529
June 25, 2019 01:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.