0

I am writing a trigger in a MySQL to conditionally create a new record in another table based on the value of field in newly added record in a table. Here is the script for the trigger (which should be self explanatory):

CREATE TRIGGER TRG_CREATE_INT_SRV_ROW
AFTER INSERT ON survey_details
FOR EACH ROW
BEGIN
 IF(New.LossTo == 'Vehicle') THEN
    INSERT INTO survey_vehicle (RefNo) VALUES (New.RefNo);
 ELSE
    INSERT INTO survey_other (RefNo) VALUES (New.RefNo);
 END IF;
END;

While running the query, the error message displayed is:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= 'Vehicle') THEN INSERT INTO interim_survey_vehicle (RefNo) VALUES (New.RefNo' at line 5

I am writing this kind of conditional trigger for the first time.

What is wrong with code please?

1 Answer 1

1

This is very straightforward.

There is no == operator.

Please change

IF(New.LossTo == 'Vehicle') THEN

to

IF(New.LossTo = 'Vehicle') THEN

UPDATE 2018-02-26 11:04 EST

You need to change the delimiter before and after the trigger:

DELIMITER $$
DROP TRIGGER IF EXISTS TRG_CREATE_INT_SRV_ROW $$
CREATE TRIGGER TRG_CREATE_INT_SRV_ROW
 AFTER INSERT ON survey_details
 FOR EACH ROW
BEGIN
 IF(New.LossTo = 'Vehicle') THEN
    INSERT INTO survey_vehicle (RefNo) VALUES (New.RefNo);
 ELSE
    INSERT INTO survey_other (RefNo) VALUES (New.RefNo);
 END IF;
END $$
DELIMITER ;

Please see my old post Unique combination key MySQL for an example

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.