CODEWITHSUNDEEP
mysqlsqltriggers
OverBakedToast
OverBakedToast

Reputation: 147

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

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.

Upvotes: 1

Views: 45

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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);

Upvotes: 1

OverBakedToast
OverBakedToast

Reputation: 147

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;

Upvotes: 1

Related Questions