4

I'm still new in msyql database trigger. I would like to ask about my lines:

CREATE TRIGGER secure_dml3
BEFORE DELETE ON t_pembelian
BEGIN
 IF (SELECT TO_CHAR (SYSDATE,'DY') IN ('SUN')) OR
 (SELECT TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '09.45' AND '20.30')
    THEN
    IF DELETING THEN
    CALL RAISE_APPLICATION_ERROR (-20502,'Perubahan pada data hanya dapat dilakukan hanya pada jam kerja.');
 END IF; 
 END IF; 
END

Mysql always say that there is an error near

'BEGIN IF (SELECT TO_CHAR (SYSDATE,'DY') IN ('SUN')) OR (SELECT TO_CHAR (SYSD' at line 3.

I really need your help. Thank you

Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
rhog23
  • 311
  • 2
  • 16
  • 1
    First thing you are missing the delimiter. So add the following at the beginning `delimiter //` and the last `END` should be as `END //` Next there is no function `TO_CHAR` in mysql and looks like you have copied some oracle trigger. And in mysql `RAISE_APPLICATION_ERROR` is not available and its called `SIGNAL` – Abhik Chakraborty Oct 21 '16 at 08:06
  • Thank you very much for your answer. I would like to ask again, to_char should be replace by what syntax? And I only need to change raise_application_error with signal? – rhog23 Oct 21 '16 at 08:20
  • Checkout mysql docs for `date_format` and `SIGNAL` for raising the error from trigger. – Abhik Chakraborty Oct 21 '16 at 08:37

1 Answers1

0

I think you are confusing oracle with MySql. In MySql doesn't exist TO_CHAR you may use DATE_FORMAT(SYSDATE,'DY')

Javi G
  • 1
  • 1