Hi,
I wrote small plugin for openfire and added also a sql-file which builds up my tables.
I want also some triggers to be inserted in the db, but whenever I put my plugin into the openfire server I get following error-log:
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 // DROP
TRIGGER IF EXISTS new_chat_message// CREATE TRIGGER new_chat’ at line
1
Obviously, there is a problem with the delimiter but I don’t know how to add a trigger without a special delimiter.
My SQL-File:
DELIMITER //
DROP TRIGGER IF EXISTS new_chat_message//
CREATE TRIGGER new_chat_message AFTER INSERT ON yeevaArchive
FOR EACH ROW BEGIN
IF (NEW.received=‘0’) THEN
INSERT INTO yeevaMsgTracker (id,from,to,time)
VALUES (NEW.id,NEW.from,NEW.to,NEW.time)
ON DUPLICATE KEY UPDATE id=NEW.id,time=NEW.time,
num=num+1;
INSERT INTO yeevaMsgCounter (user_id,buddy_id,send_num)
VALUES (NEW.from,NEW.to,1)
ON DUPLICATE KEY UPDATE send_num=send_num+1;
INSERT INTO yeevaMsgCounter (user_id,buddy_id,received_num)
VALUES (NEW.to,NEW.to,1)
ON DUPLICATE KEY UPDATE received_num=received_num+1;
END IF;
END;
//
DELIMITER ;
I also tried to load the mysql-file directly with the mysql-command and there was no problem.
Some ideas what are the reason for this or maybe a solution?
Thanks in advance,
Olli