Making a SQL Trigger that will not insert a row if it is similar to a row that already exist

Question

I am creating a trigger that will happen BEFORE and the event is INSERT in phpMyAdmin.

IF(
    SELECT * FROM packageArchive
    WHERE EXISTS(
                  SELECT * FROM packageArchive WHERE 
                  packageArchive.item = NEW.item
                  AND packageArchive.pack = NEW.pack
                  AND packageArchive.weight = NEW.weight
                  ) THEN
-- Does nothing with this insert / Doesn't insert.

If you have any suggestions I'm willing to give it a try.

Is `packageArchive` and `productsArchive` really two different tables? — Gordon Linoff, Oct 25 '17 at 18:20
@OverBakedToast You want a Trigger to fix incoming data or a Constraint to raise errors if wrong data arrive? — Juan Carlos Oropeza, Oct 25 '17 at 18:24
Looks like the best you can do is throw an exception .https://stackoverflow.com/questions/24/throw-an-error-in-a-mysql-trigger/7189396#7189396 — Juan Carlos Oropeza, Oct 25 '17 at 18:34

Gordon Linoff · Answer 1 · 2017-10-26T02:06:25.923

1

Assuming you really are using one table, then use a unique index or constraint instead:

alter table productsArchive add constraint unq_productsArchive_item_pack_weight
    unique(item, pack, weight);

Alternatively, you might want a foreign key instead:

create index idx_unq_packageArchive_item_pack_weight on packageArchive(item, pack, weight);

alter table productsArchive add constraint fk_productsArchive_item_pack_weight
    foreign key (item, pack, weight) references packageArchive(item, pack, weight);

edited Oct 26 '17 at 02:06

answered Oct 25 '17 at 18:20

Gordon Linoff

1,242,037
58
646
786

1

I think a constraint is very different to a trigger. Looks like OP just want to discard the inserted row, not raise an error. – Juan Carlos Oropeza Oct 25 '17 at 18:23
The problem with this though is that I want it to store if only one item is different. That's the reason for the ANDs they're not ORs. – OverBakedToast Oct 25 '17 at 18:28
I did try this though and it gave me a syntax error -- #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`packageArchive` (item, pack, weight)' at line 1 – OverBakedToast Oct 25 '17 at 18:36

score 1 · Accepted Answer · answered Oct 25 '17 at 18:54

I solved this with the help of @Juan Carlos Oropeza and @RuiDC and @Drew My solution looks like this

IF(
    SELECT * FROM productsArchive WHERE 
    packageArchive.item = NEW.item
    AND packageArchive.pack = NEW.pack
    AND packageArchive.weight = NEW.weight
   ) THEN
signal sqlstate '45000' set message_text = 'My Error Message';
END IF;

Making a SQL Trigger that will not insert a row if it is similar to a row that already exist

2 Answers2