mysql insert trigger only if not duplicate

Question

I am little bit confused with the usage of trigger event to get expected result

Here main table is fee. The structure is as follow

Fee

id  |   rn  |   fid |   amount  |   f_month |   year
====================================================
1   |   1   |   1   |   150000  |   1       |   1
2   |   1   |   2   |   50000   |   1       |   1
3   |   2   |   1   |   550500  |   2       |   1
4   |   2   |   2   |   200     |   2       |   1
5   |   3   |   1   |   550500  |   2       |   1

And the simply insert trigger has been used.

DROP TRIGGER IF EXISTS `insertinv`;
CREATE DEFINER=`root`@`localhost` TRIGGER `insertinv`
AFTER INSERT ON `fee` FOR EACH ROW INSERT INTO invoice VALUES(null, NEW.rn, NEW.year, '')

The output what I am getting

Invoice

inv |   rn  |   y_d |   status  
==============================
1   |   1   |   1   |   0
2   |   1   |   1   |   0
3   |   2   |   1   |   0
4   |   2   |   1   |   0
5   |   3   |   1   |   0

But I want to apply condition

if fee.rn AND fee.f_month AND fee.year is same then stop to insert. I mean ignore the fee.fid.

and achieve following result. The expected one

Invoice

inv |   rn  |   y_d |   status  
==============================
1   |   1   |   1   |   0
2   |   2   |   1   |   0
3   |   3   |   1   |   0

In trigger table inv is primary key and auto increment

You trigger syntax does not look alright. Have you given a pseudocode only ? — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 2, 2018 at 9:53
@MadhurBhaiya, I've edited, It is that what you mean, isn't it ? — Dipak
– Dipak, Commented Oct 2, 2018 at 9:57
No, write you complete trigger statement in the question. is your current trigger doing something or not ? — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 2, 2018 at 10:04
@MadhurBhaiya, current trigger is working, I've posted result - first invoice table, but what I am expected as last invoice table — Dipak
– Dipak, Commented Oct 2, 2018 at 10:12
@AnandHmt please check the answer. Since you dont have month in the invoice table, I cant consider it. — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 2, 2018 at 10:32

Madhur Bhaiya · Accepted Answer · 2018-10-02 11:52:54Z

1

Check if a inv exists for a matching year and rn.
If it does not exist, then use the insert statement.

Do the following:

DELIMITER $$
DROP TRIGGER IF EXISTS `insertinv` $$

CREATE DEFINER=`root`@`localhost` TRIGGER `insertinv`
AFTER INSERT ON `fee` 
FOR EACH ROW 
  BEGIN

    /* Declare a variable to store invoice id for matching year and rn */
    DECLARE inv_exists INT(11) DEFAULT 0;

    /* Fetch the invoice id if exists */
    SELECT inv INTO inv_exists 
    FROM invoice 
    WHERE rn = NEW.rn AND 
          y_d = NEW.year; 

    /* if no invoice exists then insert into the table */
    IF NOT(inv_exists > 0) THEN 

        /* Insert statement */
        INSERT INTO invoice VALUES(null, NEW.rn, NEW.year, '') ;

    END IF;
END $$
DELIMITER ;

edited Oct 2, 2018 at 11:52

answered Oct 2, 2018 at 10:23

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

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

2 Comments

Dipak Over a year ago

your answer not only solved my problem but also realized my structure was wrong and I found my table structure has to be revised. Huge !!! Respect to you man for your guidelines also. Please edit the typo mistake in answer semicolon is missing before the END IF. Once again thanks.

Madhur Bhaiya Over a year ago

@AnandHmt happy to help :)

Collectives™ on Stack Overflow

mysql insert trigger only if not duplicate

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related