2

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;
1
  • What's the record layout for tbl1? Commented Mar 5, 2016 at 3:54

2 Answers 2

1

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 :-).

0
AND date = NEW.date)

-->

AND tbl1.date = NEW.date)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.