CODEWITHSUNDEEP
mysqlsqltriggers
Brett Wyker
Brett Wyker

Reputation: 15

Multiple SET statements in SQL trigger using CASE?

I have a table in my SQL database that includes a column called 'event_attributes.' It is a string and each cell holds something like this:

a:4{s:10:"Discipline";s:7:"Running";s:5:"Level";s:3:"All";s:10:"Instructor";s:13:"Bill Jones";s:3:"Free";s:2:"No";}

I am trying to create a trigger that breaks this down into several new columns that look like

Disciple |  Level |  Instructor  |   Free
-----------------------------------------
Running  |  All   |  Bill Jones  |   No

Using the code below, I can get this to work for ONE set statement.

 DROP TRIGGER IF EXISTS `recode`;
 CREATE TRIGGER `recode` 
 BEFORE INSERT ON `wp_em_events` 
 FOR EACH ROW 
 SET NEW.discipline = CASE 
 WHEN NEW.event_attributes LIKE '%Boxing%' THEN 'Boxing' 
 WHEN NEW.event_attributes LIKE '%Climbing%' THEN 'Climbing' 
 WHEN NEW.event_attributes LIKE '%Cycling%' THEN 'Cycling'
 WHEN NEW.event_attributes LIKE '%Yoga%' THEN 'Yoga'   
 ELSE NEW.discipline = NULL 
 END

However, If I add something like:

 SET NEW.level = CASE

 WHEN NEW.event_attributes LIKE '%All%' THEN 'All'

 ....

I get a message saying there is an error with my SQL syntax. Anyone know how to nest two or more SET statements into one trigger? Sorry if this is a stupid question - new at this.

Thanks!

Upvotes: 1

Views: 3413

Answers (1)

sgeddes
sgeddes

Reputation: 62851

You should be using a comma to list multiple fields. Here's a condensed example:

create trigger test before insert on sometable
for each row 
set NEW.field2 = case when New.field1 = 1 then 2 else 0 end, 
    NEW.field3 = case when New.field1 = 1 then 3 else 0 end;

Btw, although your else statement technically works, you're basically setting NEW.discipline = NEW.discipline = NULL. Using else null reads a little better.

Upvotes: 2

Related Questions