MySQL Trigger Query Not Deleting As Expected

deleteMySQLtrigger

I have a test database as shown below. If date is updated in tbl1, I would like for a trigger to fire that removes all entries from tbl2 where tbl1.host = tbl2.host2 AND tbl1.date = NEW.date.

This is as close as I can get but it doesn't work. Thank you kindly in advance!

DELETE tbl2.* FROM tbl1,tbl2 
WHERE (tbl1.host = tbl2.host
AND date = NEW.date)

SAMPLE DATA 

CREATE TABLE IF NOT EXISTS tbl1 
( 
  host varchar(10) NOT NULL, 
  package1 varchar(10) NOT NULL, 
  date datetime NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DELIMITER $$
CREATE TRIGGER `Before_Insert` BEFORE INSERT ON `tbl1`
FOR EACH ROW DELETE tbl2.* FROM tbl1,tbl2 
WHERE (tbl1.host = tbl2.host AND date = NEW.date)  
-- Should be tbl2.host2 (no host field in tbl2) EDIT. 
$$
DELIMITER ;

CREATE TABLE IF NOT EXISTS `tbl2` (
  `packageid` tinyint(10) NOT NULL,
  `package2` varchar(10) NOT NULL,
  `host2` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `tbl2` (`packageid`, `package2`, `host2`) VALUES
(1, 'a', '1'),
(2, 'b', '2'),
(3, 'c', '3'),
(4, 'd', '4');

ALTER TABLE `tbl1`
  ADD PRIMARY KEY (`host`,`package1`) USING BTREE;

ALTER TABLE `tbl2`
  ADD PRIMARY KEY (`packageid`);

ALTER TABLE `tbl2`
  MODIFY `packageid` tinyint(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

Best Answer

What you want is (something like):

DELIMITER $$
CREATE TRIGGER `Before_Insert_tbl1` BEFORE INSERT ON `tbl1`
FOR EACH ROW
BEGIN 
  DELETE FROM tbl2 
  WHERE (NEW.host = tbl2.host2);  -- note not tbl1.host - out of scope!
  -- AND date = NEW.date)         -- not sure what the date logic is here.  AFAICS
                                  -- it's superfluous. Could be NEW.date = DATE(NOW());?
                                  -- i.e. delete it if it's today's record, otherwise don't?
END $$
DELIMITER ;

Your problem was having tbl1.host inside the WHERE clause where it's out of scope. The NEW refers to the new tbl1 record - no need for a table reference.

Also, as I note, the 'date' is confusing - it does nothing - tbl1.date (of the record being inserted) will automatically equal NEW.date. Finally, you appear to have made an error in your TRIGGER - there's no tbl2.host (should be "host2"?). I've edited it to make it easier for anyone else should my logic not be flawless :-).

Related Question