CODEWITHSUNDEEP
mysqldatabase-trigger
Luis Luis Maia Maia
Luis Luis Maia Maia

Reputation: 701

SQL trigger to check if row already exists

So I have two tables(orders and fabric_order). This sql is triggered after an insert is done on another table. I don't want duplicate rows, so I wanna check the table (fabric_orders) if already has a row with a code equal to the code from the other table (orders) and insert into table (fabric_order) only if doesn't exist the same code

IF NOT EXISTS
(
    select code 
    from orders
    WHERE code=fabric_order.code
)
INSERT INTO fabric_order (order_id, code, start_date)
SELECT(id, code, deliver_date)
FROM orders

Tables

Upvotes: 0

Views: 231

Answers (1)

Raymond Nijland
Raymond Nijland

Reputation: 11602

You don't need to have a trigger for it a INSERT ... SELECT ... LEFT JOIN .. should also have the same results. 

INSERT INTO fabric_order AS fabric_order_outer (
     fabric_order_outer.order_id
   , fabric_order_outer.code
   , fabric_order_outer.start_date
)
SELECT(
     orders.id
   , orders.code
   , orders.deliver_date
)
FROM orders
LEFT JOIN 
  fabric_order AS fabric_order_inner
ON
 orders.code = fabric_order_inner.code
WHERE
 fabric_order_inner.order_id IS NULL

Upvotes: 2

Related Questions