Jump to content


Photo

Trigger && SP prooblem


  • Please log in to reply
No replies to this topic

#1 char0n

char0n
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 19 May 2006 - 08:27 AM

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:
[code=auto:0]
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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users