Dynamic value selection on INSERT statement

Question

So my problem is the following. I've got a timeStatus column that will have one of two values on an INSERT statement, 'pending' or 'never', depending on whether the column fromDate is NULL or not.

I've made this prepared statement that doesn't work but represents what I intend. On the other hand I'm not sure if a constraint would be in order here, rather then having it specified on the statement. This way I could specify the status value for an insert or update and the table would know what to do. However I need some guidance as to what method to go with and where to go to learn it.

Here's the statement:

INSERT INTO Bservices (
  servStatus, timeStatus,
  fromDetails, fromDate, fromTime) 
VALUES(
  'pending', IF(ISNULL(`fromDate`)) 'pending' ELSE 'never', 
  'a', '', '')

The intended behavior is the following:

ON INSERT
if(fromDate == '') { 
  timeStatus = 'pending' 
} else { 
  timeStatus = 'never' 
}

ON UPDATE
if(timeStatus == 'pending' && fromDate != '') { 
  timeStatus = 'updated'
}

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

This doesn't work when you do it with expressions in the VALUES clause of an INSERT statement, because the expressions are evaluated before the row has been created. Therefore all columns are naturally NULL.

To do what you want, you need to write triggers BEFORE INSERT and BEFORE UPDATE. Something like the following, though I have not tested this so I'll leave debugging up to you:

CREATE TRIGGER insBservices
BEFORE INSERT ON Bservices 
FOR EACH ROW
  SET NEW.timeStatus = IF(NEW.fromDate IS NULL, 'pending', 'never');

CREATE TRIGGER updBservices
BEFORE UPDATE ON Bservices
FOR EACH ROW
  SET NEW.timeStatus = IF(NEW.fromDate IS NOT NULL AND OLD.timeStatus = 'pending', 
      'updated', NEW.timeStatus);

Re your comment:

If you want to learn more about triggers, the MySQL manual is actually pretty weak in this area. They show you the syntax for reference, but not many examples. There are a lot of tricky parts.

For example, understanding when to use DELIMITER when you define triggers, to account for the ambiguity between semicolons inside the body of a trigger, versus the terminator of the CREATE TRIGGER statement itself. This applies to CREATE PROCEDURE and CREATE FUNCTION as well.

I wrote an example and an explanation in my answer to Create function through MySQLdb.

There are tutorials on triggers, for example:

edited May 23 '17 at 12:28

Community

1
1

answered Apr 25 '14 at 16:53

Bill Karwin

538,548
86
673
828

Simple, clean and a nice way to not have to worry about it in my scripts. Thanks, now I just have to read up on TRIGGERs to figure out their full potential. Any other ways to put this to work are most welcome, as I still have plenty to learn about SQL statements and going around the documentation seems like the hardest way to go about it, since they use very technical language and most of the times I need to resort to tutorials to get the main idea. – Fernando Silva Apr 26 '14 at 14:16
Thanks, before your edit I had already gone through the documentation so I'm already implementing some behavior on triggers, seems like the next step is stored procedures and have the triggers call those instead. I'll need to go through the `DELIMITER` as I see it mentioned all over the place and I still don't have a full understanding on those. But first I'll go through your links to try and get all the info before I get too "trigger happy" and shoot myself in the foot xD – Fernando Silva Apr 26 '14 at 17:18
1

I also advise to be conservative with how much of your whole system you implement with triggers and procedures. They're inconvenient to develop, debug, and maintain, and in MySQL the implementation isn't very efficient. – Bill Karwin Apr 26 '14 at 18:47
I'll keep that in mind. Tbh I don't see many places I would implement triggers. I'm basically going for some simple things that will always happen the same way, like when I have two possible initial statuses where `default` isn't applicable, but the insert statement is already too large to add these bits of logic that won't help anyone that reads the query. Also when a table with a `has many` relationship changes to a disabled status, to cascade that status to it's members on another table, although I have a feeling that's what foreign key constraints are for... – Fernando Silva Apr 28 '14 at 10:52
1

Yes, look into `ON UPDATE CASCADE` with foreign keys. Keep in mind you can declare a foreign key to reference any key of the parent table, not only the primary key. – Bill Karwin Apr 28 '14 at 17:32
Thanks for all your help, there's just so much to learn. Wish I had known programming was so interesting before progressing my studies. Got plenty to read for now, I'll let you know if I hit a wall or I'll just post a question, thanks again – Fernando Silva Apr 28 '14 at 17:51

Dynamic value selection on INSERT statement

1 Answers1