samedi 18 juin 2016

How to create a trigger for mysql 5.5.44?

s = "CREATE TABLE " + tableName +"n" +
    "(n" +
    "    " + tablelower + "_currentid INT PRIMARY KEY AUTO_INCREMENT,n" +
    "    " + tablelower + "_id VARCHAR(8) NOT NULL,n" +
    "    " + tablelower + "_name VARCHAR(45) NOT NULL,n" +
    "    " + tablelower + "_type VARCHAR(45) NOT NULL,n" +
    "    " + tablelower + "_topic  VARCHAR(255) NOT NULL,n" +
    "    " + tablelower + "_pin VARCHAR(6) NOT NULL,n" +
    "    " + tablelower + "_device VARCHAR(100) NOT NULL,n" +
    "    " + tablelower + "_device_id INT NOT NULL,n" +
    "    FOREIGN KEY(" + tablelower + "_device_id) REFERENCES Devices(device_currentid)n" +
    ");n" +
    "n" +
    " delimiter | n" +
    " CREATE TRIGGER " + tablelower + "_trigger BEFORE INSERT ON " + tableName + 
    " FOR EACH ROWn" +
    " BEGINn" +
    "   SET new." + tablelower + "_id = CONCAT('" + topic + "',LPAD((SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '" + tableName + "'),4,'0'));n" +
    "   SET new." + tablelower + "_topic = CONCAT((SELECT device_topic FROM Devices WHERE device_name LIKE new." + tablelower + "_device),'/',(new." + tablelower + "_id));n" +
    " END;n" +
    " | n" +
    " delimiter ;";

    mysqlconn.createStatement().execute(s);

Above is the Code which will make a table and a trigger for it with the given name to tableName and tablelower which are string variables. This is the first version i wrote and i was getting the error below:

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 'delimiter | 
CREATE TRIGGER tablename_trigger BEFORE INSERT ON tablename FO' at line 14

After google help I've found this thread Error while creating trigger through JDBC on mysql5.5 and the doc http://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html and i've changed my code to a StringBuilder to this:

            tableCreation.append("CREATE TABLE " + tableName);
            tableCreation.append("(");
            tableCreation.append(tablelower + "_currentid INT PRIMARY KEY AUTO_INCREMENT,");
            tableCreation.append(tablelower + "_id VARCHAR(10) NOT NULL,");
            tableCreation.append(tablelower + "_name VARCHAR(45) NOT NULL,");
            tableCreation.append(tablelower + "_type VARCHAR(45) NOT NULL,");
            tableCreation.append(tablelower + "_topic  VARCHAR(255) NOT NULL,");
            tableCreation.append(tablelower + "_pin VARCHAR(6) NOT NULL,");
            tableCreation.append(tablelower + "_device VARCHAR(100) NOT NULL,");
            tableCreation.append(tablelower + "_device_id INT NOT NULL,");
            tableCreation.append("FOREIGN KEY(" + tablelower + "_device_id) REFERENCES Devices(device_currentid)");
            tableCreation.append(" ); ");

            tableCreation.append("DELIMITER // ");
            tableCreation.append(" CREATE");
            tableCreation.append(" TRIGGER " + tablelower + "id_trigger ");
            tableCreation.append(" BEFORE INSERT");
            tableCreation.append(" ON " + tableName + " FOR EACH ROW");
            tableCreation.append(" BEGIN");
            tableCreation.append(" SET new." + tablelower + "_id = CONCAT('" + topic + "',LPAD((SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '" + tableName + "'),4,'0'));");
            tableCreation.append(" SET new." + tablelower + "_topic = CONCAT((SELECT device_topic FROM Devices WHERE device_name LIKE new." + tablelower + "_device),'/',(new." + tablelower + "_id));");
            tableCreation.append(" END;//");
            tableCreation.append("DELIMITER ; ");

            mysqlconn.createStatement().execute(tableCreation.toString());

But still after these changes i get this error:

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 'DELIMITER // CREATE TRIGGER reedswitchid_trigger BEFORE INSERT ON ReedSwitches' at line 1.

I am executing this through java and the MySQL server is on a Raspberry pi 2. For any more info comment and NOTE that i am a beginner in SQL. Thanks

Aucun commentaire:

Enregistrer un commentaire