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