Mysql dropping inserts with triggers
- by user2891127
Using mysql 5.5. I have two tables. One has a whitelist of hashes. When I insert a new row into the other table, I want to first compare the hash in the insert statement to the whitelist. If it's in the whitelist, I don't want to do the insert (less data to plow through later). The inserts are generated from another program and are text files with sql statements.
I've been playing with triggers, and almost have it working:
BEGIN
IF (select count(md5hash) from whitelist where md5hash=new.md5hash) 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Already Whitelisted';
END IF;
END
But there's a problem. The Signal throwing up the error stops the import. I want to skip that line, not stop the whole import.
Some searching didn't find any way to silently skip the import.
My next idea was to create a duplicate table definition, and redirect the insert to that dup table. But the old and new don't seem to apply to table names.
Other then adding an ignore column to my table then doing a mass drop based on that column after the import, is there any way to achieve my goal?
