0

I have a MySQL 5.0 server. I want to build a BEFORE DELETE TRIGGER that will ignore a delete statement if a condition exists.

I do not need to return a message if the DELETE fails the condition.

All the examples I have found use SIGNAL SQLSTATE to throw an error. Sound great, only MySQL 5.0 does not support SIGNAL SQLSTATE.

DELIMITER $$

CREATE DEFINER=`myname`@`%` 
TRIGGER  `tbtimecard_BDEL` 
BEFORE DELETE ON  `tbtimecard`
FOR EACH ROW
BEGIN
    if date(old.dt_of_entry) <> curdate() then
        delete from `rhi_sap`.`tbtimecard` where id = 0;

    END IF;
END$$
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
Gene
  • 57
  • 6
  • Possible duplicate of [How to write a trigger to abort delete in MYSQL?](https://stackoverflow.com/questions/7595714/how-to-write-a-trigger-to-abort-delete-in-mysql) – Madhur Bhaiya Jul 16 '19 at 15:00
  • I'm not sure this approach can even work, as the trigger is operating on the same table scoped by the trigger. – Tim Biegeleisen Jul 16 '19 at 15:00
  • ^^ Check the hack of calling an undefined procedure to trigger error in the above link. – Madhur Bhaiya Jul 16 '19 at 15:01
  • You're using a version of MySQL released in 2005. I recommend you just upgrade. Not only do you need at least this feature that is provided by newer versions, but the version you're using has critical security bugs. – Bill Karwin Jul 16 '19 at 15:08
  • According to documention, I found on MySQL 5.0, the trigger cannot use the CALL statement. – Gene Jul 16 '19 at 15:11
  • Upgrading is a not an option... I wish it was. – Gene Jul 16 '19 at 15:11
  • Tim Biegeleisen - I understand.... What approach would you use? All I need to do is ignore the delete if the condition exists. I have no requirement to return a message. – Gene Jul 16 '19 at 15:15
  • To Madhur's statement, the solution you gave, uses the CALL statement. MySQL 5.0 does not allow the use of a CALL in a trigger. – Gene Jul 16 '19 at 15:19
  • 1
    @Gene ohho..anyways, the general idea in that solution is to do something which causes the trigger to fail. check this [answer](https://stackoverflow.com/a/703505/2469308) and other answers on that question.. – Madhur Bhaiya Jul 16 '19 at 17:29
  • 1
    Exactly.. `SELECT non_existing_function('error description for debugging') INTO @nirvana;` – Paul Spiegel Jul 16 '19 at 19:21
  • It's not perfect, but it works. Throws an error back... I was hoping for no message.. Under the circumstances, I will call it a win. I do realize running on such an old version of MySQL is not ideal, but that is not my decision to upgrade. – Gene Jul 16 '19 at 20:22

0 Answers0