473,320 Members | 1,955 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 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 1418

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: level8 | last post by:
Hi, Everybody, I'm a Hungarian SQL user and I need a little help for SQL Server 7 ! I protect my table against bad data with a trigger. I use ROLLBACK and RAISERROR statement in this trigger....
8
by: Jason | last post by:
I have a table that matches up Securities and Exchanges. Individual securities can belong on multiple exchanges. One of the columns, named PrimaryExchangeFlag, indicates if a particular exchange is...
1
by: Jen S | last post by:
I feel like I'm missing something obvious here, but I'm stumped... I have a stored procedure with code that looks like: INSERT INTO MyTableA ( ...fields... ) VALUES (...values...) IF...
0
by: Dave Sisk | last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table. (Note this is an external trigger defined with the ADDPFTRG CL command, not a SQL trigger defined with the CREATE...
18
by: Bill Smith | last post by:
The initial row is inserted with the colPartNum column containing a valid LIKE pattern, such as (without the single quotes) 'AB%DE'. I want to update the column value with the results of a query...
4
by: Alexander Pope | last post by:
I am using db2 udb v8.2 AIX I have created trigger, however I am not confident it meets industry standards. If I make it fail, I cant tell from the message where it is failing. what can I add to...
0
by: JohnO | last post by:
Thanks to Serge and MarkB for recent tips and suggestions. Ive rolled together a few stored procedures to assist with creating audit triggers automagically. Hope someone finds this as useful as...
6
by: Antanas | last post by:
I am puzzled by the following behavior. If stored procedure PROC1 is called 2 times in trigger TRIG1, then I get error during trigger firing: SQL0723N An error occurred in a triggered SQL...
3
by: yinzara | last post by:
I have the following trigger that calls a DB2 stored procedure: DROP TRIGGER GGWU.TRI_A_MULTI_PROP@ CREATE TRIGGER GGWU.TRI_A_MULTI_PROP AFTER INSERT ON GGWU.MULTIPLIER_PROPERTY REFERENCING ...
3
by: lenygold via DBMonster.com | last post by:
Thank you very much SERGE for your help. I found example in Graeme Birchall COOKBOOK wich i think exactly what i need for SQL check in triggers: • User query joins to table function - sends...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.