error in trigger (mysql)

Question

I am trying to create a trigger with an IF condition and I am getting an error on line 6.

DELIMITER $$

CREATE TRIGGER trgSoftwareLicenseDetails
AFTER UPDATE ON SoftwareLicenseDetails
FOR EACH ROW BEGIN
IF new.Flag = 0 THEN
    INSERT INTO audithistory (audit_date, audit_field, audit_oldvalue, audit_changelog_fk, audit_newvalue, audit_assetid_fk) VALUES (Now(),'Software License Details', (SELECT Title FROM SoftwareTypes WHERE ID = 
    (SELECT SoftwareNameFK FROM SoftwareLicenseDetails
    WHERE  ComputerFK=new.ComputerFK
    ORDER BY ID Desc
    LIMIT 1)), (SELECT MAX(ID) FROM Changelog as ChangelogID ), 'License Added',new.ComputerFK);
FROM SoftwareLicenseDetails
WHERE  ComputerFK=new.ComputerFK
ORDER BY ID Desc
LIMIT 1)), (SELECT MAX(ID) FROM Changelog as ChangelogID ), 'License Added',new.ComputerFK);
ELSE IF new.Flag = 1 THEN
    INSERT INTO audithistory (audit_date, audit_field, audit_oldvalue, audit_changelog_fk, audit_newvalue, audit_assetid_fk) VALUES (Now(),'Software License Details', 'N/A', (SELECT MAX(ID) FROM Changelog as ChangelogID ), 'License Deleted',old.ComputerFK);
END IF;
END; $$
DELIMITER ;

Error:

Error Code: 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 'FROM SoftwareLicenseDetails WHERE ComputerFK=new.ComputerFK ORDER BY ID Desc LI' at line 6 0.000 sec

What am I missing?

What error do you get?

Domysee
– Domysee

2015-11-12 10:42:33 +00:00
Commented Nov 12, 2015 at 10:42 — Domysee
– Domysee, Commented Nov 12, 2015 at 10:42

Abhishek · Accepted Answer · 2015-11-12 10:44:58Z

1

Check this:

DELIMITER $$

CREATE TRIGGER trgSoftwareLicenseDetails
AFTER UPDATE ON SoftwareLicenseDetails
FOR EACH ROW 

BEGIN

    IF new.Flag = 0 THEN
        INSERT INTO audithistory (audit_date, audit_field, audit_oldvalue, audit_changelog_fk, audit_newvalue, audit_assetid_fk) VALUES (Now(),'Software License Details', (SELECT Title FROM SoftwareTypes WHERE ID = 
        (SELECT SoftwareNameFK FROM SoftwareLicenseDetails
        WHERE  ComputerFK=new.ComputerFK
        ORDER BY ID Desc
        LIMIT 1)), (SELECT MAX(ID) FROM Changelog as ChangelogID ), 'License Added',new.ComputerFK);

    ELSEIF new.Flag = 1 THEN
        INSERT INTO audithistory (audit_date, audit_field, audit_oldvalue, audit_changelog_fk, audit_newvalue, audit_assetid_fk) VALUES (Now(),'Software License Details', 'N/A', (SELECT MAX(ID) FROM Changelog as ChangelogID ), 'License Deleted',old.ComputerFK);
    END IF;

END; $$
DELIMITER ;

answered Nov 12, 2015 at 10:44

Abhishek

4,8094 gold badges29 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brian Over a year ago

Thanks that worked! So there is no space between the ELSE and the IF.

Abhishek Over a year ago

@Brian: Please accept the answer if you find it correct.

Collectives™ on Stack Overflow

error in trigger (mysql)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related