Is there any way to check whether or not the query

After the row is inserted into the table A, I need the trigger to insert some values from it into the other table B, but only if table B doesn't already contain the row with such combination of values. So here's what I came up with:

CREATE TRIGGER upd_manufactorer_in_category AFTER INSERT ON product
FOR EACH ROW 
BEGIN
  DECLARE id_category INT; 
  DECLARE id_manufacturer INT;
  DECLARE id_category_test INT; 

  SET id_category=NEW.id_category;
  SET id_manufacturer=NEW.id_manufacturer;

  SET id_category_test=(SELECT 'id_category'
                FROM category_has_manufacturer
                WHERE 'id_category'=id_category
                AND 'id_manufacturer'=id_manufacturer);

  IF id_category_test IS NULL
  THEN
    INSERT INTO category_has_manufacturer
    SET category_has_manufacturer.id_category = id_category, 
    category_has_manufacturer.id_manufacturer = id_manufacturer;
  END IF;
END

And it works, it inserts the row in the table. But it doesn't check whether it existed before, so I have many duplicates. Of course I can query this with DISTICT, but this is not what I want to achieve here. I want to have a table with only unique (id_category, id_manufacturer) pairs. Can anybody help me?

Two things you can do both will help.

First add a unique index/key to the table as Devart suggested.

The second thing is that this is a great place where you can use right and left joins to get the data that does not exist. I am going to show you the theory then you can apply it to your answer as a exercise.

Step 1:

Create a database called test.

Step 2:

Run the following two create table scripts in the newly created test database:

CREATE  TABLE `test`.`category_has_manufacturer_A` 
(
      `id_category` INT NOT NULL ,
      `id_manufacturer` INT NOT NULL ,
       PRIMARY KEY (
                     `id_category`, 
                     `id_manufacturer`
                   ) 
);

CREATE  TABLE `test`.`category_has_manufacturer_B` 
(
      `id_category` INT NOT NULL ,
      `id_manufacturer` INT NOT NULL ,
       PRIMARY KEY (
                     `id_category`, 
                     `id_manufacturer`
                   ) 
);

Right so we two tables that have have a columns in common that we can join on.

Step 3:

Add some data to the category_has_manufacturer_a table

INSERT INTO `test`.`category_has_manufacturer_a`
(
  `id_category`,
  `id_manufacturer`
)
VALUES
(
    1,
    1
);

Cool so Table A has some data in that we want to add to Table B.

Step 4:

Lets do a inner join on the two tables and look at the result run the following query.

SELECT
   A.`id_category` AS A_ID_CATEGORY,
   A.`id_manufacturer` AS A_ID_MANAFACTURER,
   B.`id_category` AS B_ID_CATEGORY,
   B.`id_manufacturer` AS B_ID_MANAFACTURER
FROM category_has_manufacturer_a A  
INNER JOIN  category_has_manufacturer_b B
     ON A.`id_category` =B.`id_category`
     AND A.`id_manufacturer`=B.`id_manufacturer`

Great so there was no data returned as there was no direct match between the rows in Table A and Table B this is due to the fact that a inner join only returns rows that match the JOIN condition.

Step 5:

The power of OUTER JOINS revealed. A OUTER join will return data even if they dont match. In this case we are going to do a LEFT OUTER JOIN on Table A and Table B. As Table A is the LEFT table we are asking to return all the data from Table A even if they dont match anything in Table B. Run the following query.

SELECT
   A.`id_category` AS A_ID_CATEGORY,
   A.`id_manufacturer` AS A_ID_MANAFACTURER,
   B.`id_category` AS B_ID_CATEGORY,
   B.`id_manufacturer` AS B_ID_MANAFACTURER
FROM category_has_manufacturer_a A  
LEFT OUTER JOIN  category_has_manufacturer_b B
     ON A.`id_category` =B.`id_category`
     AND A.`id_manufacturer`=B.`id_manufacturer`

This will return a recordset that looks like this:

A_ID_CATEGORY|A_ID_MANAFACTURER|B_ID_CATEGORY|B_ID_MANAFACTURER|
            1|                1|             |                 |

This result clearly shows that Table A has rows in that dont match rows in Table B.

Step 6:

Now we can use the SELECT statement in Step 5 to create a INSERT statement to insert these rows that dont exist. Run the following query:

 INSERT INTO category_has_manufacturer_b
 SELECT
   A.`id_category` AS A_ID_CATEGORY,
   A.`id_manufacturer` AS A_ID_MANAFACTURER

 FROM category_has_manufacturer_a A  
 LEFT OUTER JOIN  category_has_manufacturer_b B
     ON A.`id_category` =B.`id_category`
     AND A.`id_manufacturer`=B.`id_manufacturer`

Lets check what the insert did run the following query:

SELECT
 `category_has_manufacturer_b`.`id_category`,
 `category_has_manufacturer_b`.`id_manufacturer`
FROM `test`.`category_has_manufacturer_b`;

You will see a result set with the row from Table A now inserted into Table B.

Step 7:

Here we add the last bit and pieces where we make sure we add only new rows from Table A to Table B. First lets add some more data. Run the following query:

INSERT INTO `test`.`category_has_manufacturer_a`
(
   `id_category`,
   `id_manufacturer`
)
VALUES
(
    1,

    2
);

Now run the query with the left join in again:

SELECT
   A.`id_category` AS A_ID_CATEGORY,
   A.`id_manufacturer` AS A_ID_MANAFACTURER,
   B.`id_category` AS B_ID_CATEGORY,
   B.`id_manufacturer` AS B_ID_MANAFACTURER
FROM category_has_manufacturer_a A  
LEFT OUTER JOIN  category_has_manufacturer_b B
     ON A.`id_category` =B.`id_category`
     AND A.`id_manufacturer`=B.`id_manufacturer`

You will see a resultset with the row that matches in both tables and the new row in table a only. If you ran the insert now you would insert both rows and have a primary key violation or in your case a duplicate row.

If you change the query above to the following and run it:

 SELECT
   A.`id_category` AS A_ID_CATEGORY,
   A.`id_manufacturer` AS A_ID_MANAFACTURER,
   B.`id_category` AS B_ID_CATEGORY,
   B.`id_manufacturer` AS B_ID_MANAFACTURER
FROM category_has_manufacturer_a A  
LEFT OUTER JOIN  category_has_manufacturer_b B
     ON A.`id_category` =B.`id_category`
     AND A.`id_manufacturer`=B.`id_manufacturer`
WHERE B.`id_category` IS NULL AND  B.`id_manufacturer` IS NULL

The record set will contain only the new record in Table A.

Change the insert statement to:

INSERT INTO category_has_manufacturer_b
SELECT
   A.`id_category` AS A_ID_CATEGORY,
   A.`id_manufacturer` AS A_ID_MANAFACTURER,
   B.`id_category` AS B_ID_CATEGORY,
   B.`id_manufacturer` AS B_ID_MANAFACTURER
FROM category_has_manufacturer_a A  
LEFT OUTER JOIN  category_has_manufacturer_b B
     ON A.`id_category` =B.`id_category`
     AND A.`id_manufacturer`=B.`id_manufacturer`
WHERE B.`id_category` IS NULL AND  B.`id_manufacturer` IS NULL

When you run this INSERT it will only insert the new rows from Table A into Table B.

Summary: LEFT and RIGHT JOIN statments can be used to take two tables compare them and return only the new rows from one. This is a great technique when trying to add from one table to the other table if the rows are new. It will work on one row and multiple rows at the same time and since it is set based (SQL is good at this) it is super fast.

I hope you understand what is happening and can apply this in your trigger.

TIP: In a trigger you have access to the NEW table you can join to this to determine the new rows.

Have fun!