472,133 Members | 1,458 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,133 software developers and data experts.

trigger/stored procedure error

api
Hello,

I never worked with SP/triggers in MySQL, I have this problem. On
webpage I'd like to show online users, so idea is very simple: in one
select (SP) make all necesary things. I begin with:

CREATE TRIGGER online_insert
AFTER UPDATE ON subs_online
FOR EACH ROW
DELETE FROM subs_online WHERE ts < (unix_timestamp() - 180);
--------------
CREATE PROCEDURE online_insert(ip char(15))
INSERT INTO subs_online
(ip, ts) VALUES (ip, unix_timestamp())
ON DUPLICATE KEY UPDATE ts=unix_timestamp();

IN PHP: $this->db->DbQuery("call
online_insert('{$_SERVER['REMOTE_ADDR']}')");

But I get error:
query: call online_insert('84.47.19.168')
error: Can't update table 'subs_online' in stored function/trigger
because it is already used by statement which invoked this stored
function/trigger.

I tried change "AFTER UPDATE" to something else, but nothing worked as
expected.

Could someone help me on this issue please ?

Thanks a lot.

May 17 '06 #1
0 1384

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by level8 | last post: by
8 posts views Thread by Jason | last post: by
18 posts views Thread by Bill Smith | last post: by
4 posts views Thread by Alexander Pope | last post: by
reply views Thread by JohnO | last post: by
3 posts views Thread by lenygold via DBMonster.com | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.