SQL Triggers

A trigger is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

Common sense, as when the table has a row inserted, the trigger would insert the new value in the same row, then trigger itself again with the triggered insert. Infinite loop.

I would have to say it's not possible then.

Some ideas you probably will have though of:
Have two tables, the main (usual) one and another only to store the new value with auto_inc id.

Run a second query using mysql_insert_id(), append to VALUE and update.

Or just insert the value without auto_inc_ID, then whenever using a select query, get the row ID and append it to the VALUE.

I agree with the possibility of doing and insert while an insert trigger would cause a loop, but you'd think you could issue an update from an insert as long as you don't have an update trigger

Yes, thought of each of those items, but for what this is being used for the client access the row though a default "row editor", so was wanting to do this without having to write a custom editor for this.

On my home server, I'm going to try havig the second table, that gets updated with the AFTER INSERT. and then on that one, have an AFTER UPDATE which does an update back to the first... Just curious if the server will let this happen or not. For production, I don't like that idea.

For now I found a spot to throw a second SQL to preform the update, however it does an update on all rows, and since records auto delete after a set period of time, will never be more than 100 rows.

I'm still looking for another way though...

-Greg

Why not concat it on the selecting?

SELECT CONCAT("whatever.php?event=",EventID) AS EventURL FROM tblEvent

Or, depending on how you are displaying the data, concat it in the HTML template/PHP string/whatever.

I'm still a little... not sure of the emotion.. that MySQL doesn't support your trigger. I'm not buying the 'infinite loop' argument, no matter who's selling.

You're trigger is fired 'on insert'
you're trigger is performing an update.

You know, in a sane db that has sequences, you could just:

insert into test
(id,
val)
values
(nextval('sequence_name')
, 'url?id=' || currval('sequence_name'));

I know, you're all jealous of my MySQL free environment now, aren't you! -- this post provided no help to anyone... anywhere. It's been a long week.

Cheers,
Shaggy

SELECT CONCAT("whatever.php?event=",EventID) AS EventURL FROM tblEvent

Building on pr0g's idea, you could create a view using that concat logic. Your client's editor probably wouldn't know the difference between a view and a table.

Another thing you might want to try is putting your update logic in a stored proc, and calling the stored proc in your trigger.

Triggers are used to perform particular action whenever some insert, update, delete commands executed, they are used to control, monitor and manage group of tables.

An SQL trigger cannot be directly called from an application. An SQL trigger is invoked by the database management system on the execution of a triggering insert, update, or delete operation.

thanks for info

Hello Gusy......
Look keyword so some write content and click linking keyword so take more than more knowledge.....................