CODEWITHSUNDEEP
mysqltriggers
Ganesh RJ
Ganesh RJ

Reputation: 942

error in trigger creation also how to optimise it

Please tell me whats wrong with the code. Not able to fine the bug

DELIMITER $$
CREATE TRIGGER update_status  BEFORE Update ON listing_basic_new_updated 
FOR EACH ROW
 if new.processing_status is not null 
    then begin
    SET new.rep_status = New.processing_status;
    end; 
  elseif new.televeri_status is not null 
    then begin
    SET new.rep_status = New.televeri_status;
    end; 
  elseif new.verification_status is not null 
    then begin
    SET new.rep_status = New.verification_status;
    end;
end if;
END$$
DELIMITER ;

Upvotes: 0

Views: 26

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

I think you're missing a BEGIN that would match up to your END at the end:

DELIMITER $$
CREATE TRIGGER update_status  BEFORE Update ON listing_basic_new_updated 
FOR EACH ROW
BEGIN
 if new.processing_status is not null 
    then begin
    SET new.rep_status = New.processing_status;
    end; 
  elseif new.televeri_status is not null 
    then begin
    SET new.rep_status = New.televeri_status;
    end; 
  elseif new.verification_status is not null 
    then begin
    SET new.rep_status = New.verification_status;
    end;
end if;
END$$
DELIMITER ;

I think you might be able to replace all of it with

SET new.rep_status = COALESCE(new.processing_status, new.televeri_status,
                           new.verification_status, new.rep_status);

COALESCE: "Returns the first non-NULL value in the list, or NULL if there are no non-NULL values."

Upvotes: 1

Related Questions