MySQL Triggers and Django

Django is a great Web Framework to build websites in. It handles so many things for you that sometimes it can’t handle the most basic things. If you have ever tried to use Triggers in MySQL and Django, you know what I mean (or will soon find out). Now, you may not have many uses for Triggers, especially when Django handles the majority of the work for your automatically, but in some cases, it is neccessary to define your own triggers at the database level (like for a Database course in college).

Django provides you with this really nice “syncdb” command, but there isn’t an obvious way to to insert custom triggers. Searching around, I found that you can provide “custom” sql during the process, which would seem like a great place to insert triggers. Just add a “sql/” folder to your Django app, and create a file called <model_name>.sql. Or, if you want to be more specific, <model_name>.mysql.sql. At first I thought this would be a great place for the trigger, but it didn’t work. Django kept getting hungup on the ‘;’ in the Trigger.

When creating a Trigger using a GUI like MySQL Workbench or ProSequel, the application sends SQL commands separately,  as delimited by the ‘;’. The problem is that a ‘;’ may exist within a Trigger statement. For example, the following would not work:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TRIGGER validate_enrollment_hours
BEFORE INSERT ON service_serviceenrollment
FOR EACH ROW
BEGIN
DECLARE event_start datetime;
DECLARE event_end datetime;
SELECT start_time, end_time INTO event_start, event_end FROM events_event WHERE id = NEW.event_id;
IF NEW.start < event_start OR NEW.end > event_end THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid Start/End Time', MYSQL_ERRNO = 1001;
END IF;
END;

I spent the better part of an hour trying to figure out why. Turns out the ‘;’s really confused SequelPro and MySQL Workbench. The solution was to change the delimiter and execute the following statement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
delimiter |
CREATE TRIGGER validate_enrollment_hours
BEFORE INSERT ON service_serviceenrollment
FOR EACH ROW
BEGIN
DECLARE event_start datetime;
DECLARE event_end datetime;
SELECT start_time, end_time INTO event_start, event_end FROM events_event WHERE id = NEW.event_id;
IF NEW.start < event_start OR NEW.end > event_end THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid Start/End Time', MYSQL_ERRNO = 1001;
END IF;
END;
|

Great! Now I can actually get this Trigger in the database, but if I use the latter example in the <model_name>.mysql.sql file, it still doesn’t work! Finally, I stumbled upon ticket #3214 on the Django website. While I wasn’t too keen on doing any kind of patch, there was a interesting little snippet I read in there.

“As a workaround, multi-line SQL statements have to have someting other than whitespace between their semicolons and newline characters.” -Sam Morris

Eureka! Finally!

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TRIGGER validate_enrollment_hours
BEFORE INSERT ON service_serviceenrollment
FOR EACH ROW
BEGIN
DECLARE event_start datetime; --
DECLARE event_end datetime; --
SELECT start_time, end_time INTO event_start, event_end FROM events_event WHERE id = NEW.event_id; --
IF NEW.start < event_start OR NEW.end > event_end THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid Start/End Time', MYSQL_ERRNO = 1001; --
END IF; --
END;

All is well now. The apocalypse has been diverted, and I can finally move on to the next million other things I need to do.

Trackbacks

  1. […] am trying to create a MySQL trigger in my Django application. I tried to follow the example at http://bryanmarty.com/blog/2012/12/03/mysql-triggers-django/ , but when my custom SQL is run, I do not see the trigger created in the database (using show […]

Speak Your Mind

*

13 + 14 =

Privacy Policy