Create Trigger Mysql After Insert issue

Question

I had an issue when I tried to add mysql trigger query via codeigniter $this->db->query(); but it shows syntax error. Here is code below:

  $this->db->query("DELIMITER $$ 
        CREATE TRIGGER `after_stuload_insert_info_data` AFTER INSERT 
        ON `{$this->tables->hesa_stuload_student_info}`
         FOR EACH ROW BEGIN 
          INSERT INTO `{$this->tables->hesa_student_reportperiod}` (hesa_course_relation_instance_id, student_data_id)
           VALUES (new.hesa_course_relation_instance_id, new.student_data_id); 
         END$$  
         DELIMITER ; ");

Can you Please tell what is the wrong with this code. Thanks in advance. Check below for the error.

How about showing us the error? – Uueerdo Aug 04 '16 at 20:40 — Uueerdo, Aug 04 '16 at 20:40
edited with error image. – Md Emrul Easir Aug 04 '16 at 21:31 — Md Emrul Easir, Aug 04 '16 at 21:31

score 1 · Accepted Answer · answered Aug 04 '16 at 23:56

DELIMITER is a directive for clients that have a parser looking for semicolons. Using a script, you don't use this at all. Nor do you put the $$ at the end.

Just this:

$this->db->query("CREATE TRIGGER
... the rest of the trigger ...
END");

Also, if you're using a variable for the table name (Red flag! Why are you creating triggers from web code?) you should probably also dynamically generate the trigger name. Trigger names have to be unique within a schema.

score 0 · Answer 2 · edited May 23 '17 at 12:16

0

You usually cannot execute multiple queries in a single request. Most likely you need to do something like this:

  $this->db->query("DELIMITER $$"); 
  $this->db->query("CREATE TRIGGER `after_stuload_insert_info_data` AFTER INSERT 
        ON `{$this->tables->hesa_stuload_student_info}`
         FOR EACH ROW BEGIN 
          INSERT INTO `{$this->tables->hesa_student_reportperiod}` (hesa_course_relation_instance_id, student_data_id)
           VALUES (new.hesa_course_relation_instance_id, new.student_data_id); 
         END$$"); 
  $this->db->query("DELIMITER ; ");

According to another answer here you don't even need the delimiter statements, when creating triggers in this kind of environment, at all.

edited May 23 '17 at 12:16

Community

1
1

answered Aug 04 '16 at 21:35

Uueerdo

15,723
1
16
21

Nop no luck same syntax error type. – Md Emrul Easir Aug 04 '16 at 21:42
@EmrulTheDemon That is a generic error, if the accompanying message changed it may be relevant. – Uueerdo Aug 04 '16 at 21:47
when I remove DELIMITER from the php file and try it. server getting busy and not showing the page, also when I try it by phpmyadmin it shows Error in request processing. 500 internal error. – Md Emrul Easir Aug 04 '16 at 21:47

Create Trigger Mysql After Insert issue

2 Answers2