CODEWITHSUNDEEP
mysqlphpmyadmin
gopal
gopal

Reputation: 89

how to create an trigger in MySQL to insert new row only if not exists?

what I want is to insert a new row only if it does not exist on that date then it should be ignored. this table does not have the primary and unique key I am using it in this table bar_opening_details. I can't add such primary and unique constraints here.to INSERT IGNORE. since this daily wise entry of items with the same item_id.

CREATE definer=`root`@`localhost` TRIGGER `store`.`bar_recvd_details_after_insert` beforeINSERT
on `bar_recvd_details` FOR each row BEGIN
DECLARE mydate  date;DECLARE mydate1 DATE;DECLARE myid    INT;SELECT Max(close_date)
INTO   mydate
FROM   bar_opening_details;SELECT item_id
INTO   myid
FROM   bar_opening_details
WHERE  op_date=mydate
AND    item_id=new.item_id;SELECT op_date
INTO   mydate1
FROM   bar_opening_details
WHERE  op_date=mydate
AND    item_id=new.item_id;IF(myid != new.item_id
AND
mydate1 != mydate) then
INSERT INTO `store`.`bar_opening_details`
            (
                        `item_cid`,
                        `item_id`,
                        `op_date`,
                        `op_value`,
                        `close_date`,
                        `close_val`
            )
            VALUES
            (
                        new.item_cid,
                        new.item_id,
                        mydate,
                        '0',
                        mydate,
                        '0'
            );ENDIF;END

Upvotes: 0

Views: 401

Answers (1)

gopal
gopal

Reputation: 89

Solved it by this

CREATE DEFINER=`root`@`localhost` TRIGGER `store`.`bar_recvd_details_BEFORE_INSERT` BEFORE INSERT ON `bar_recvd_details` FOR EACH ROW
BEGIN
declare mydate date;
SELECT max(close_date) into mydate FROM bar_opening_details;
 
 IF NOT EXISTS (SELECT 1 FROM bar_opening_details WHERE item_id = NEW.item_id and op_date=mydate) THEN
    INSERT INTO bar_opening_details (item_cid,
item_id,
op_date,
op_value,
close_date,
close_val)
    VALUES (NEW.item_cid,NEW.item_id,mydate,'0',mydate,'0');
END IF;
END

Upvotes: 1

Related Questions