CODEWITHSUNDEEP
phpmysql
Sarz
Sarz

Reputation: 1976

Triggers If statement syntax error

I am new in MySQL PHP WAMP, my trigger is created successfully without if statement, it gives error when I execute it error is :

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (NEW.userloc_lat ='91.000000' AND NEW.userloc_lan = '181.000000') THEN ' at line 6

DELIMITER $$
CREATE TRIGGER before_new_userloc
    BEFORE INSERT ON tbl_userloc
    FOR EACH ROW BEGIN

    INSERT INTO tbl_unknown_site
    IF (NEW.userloc_lat ='91.000000' AND NEW.userloc_lan = '181.000000')
    THEN SET 
    unknown_site_lat = NEW.userloc_lat,
    unknown_site_lon = NEW.userloc_lon,
    unknown_site_stt = NOW(),
    unknown_site_dtm = NEW.userloc_dtm,
    unknown_site_appid = NEW.userloc_appid,
    unknown_site_cellid = NEW.userloc_cellid,
    unknown_site_lacid = NEW.userloc_lacid,
    unknown_site_subid = NEW.userloc_id;

END$$
DELIMITER ;

I think there would be only syntanx error what i am looking at.

Data type of userloc_lat and userloc_lon is decimal(10,6)

Upvotes: 1

Views: 63

Answers (1)

Barmar
Barmar

Reputation: 781058

IF should be before INSERT:

DELIMITER $$
CREATE TRIGGER before_new_userloc
    BEFORE INSERT ON tbl_userloc
    FOR EACH ROW BEGIN
        IF (NEW.userloc_lat ='91.000000' AND NEW.userloc_lon = '181.000000')
        THEN
            INSERT INTO tbl_unknown_site
            SET 
                unknown_site_lat = NEW.userloc_lat,
                unknown_site_lon = NEW.userloc_lon,
                unknown_site_stt = NOW(),
                unknown_site_dtm = NEW.userloc_dtm,
                unknown_site_appid = NEW.userloc_appid,
                unknown_site_cellid = NEW.userloc_cellid,
                unknown_site_lacid = NEW.userloc_lacid,
                unknown_site_subid = NEW.userloc_id;
        END IF;

    END$$
DELIMITER ;

Upvotes: 1

Related Questions