SQL Triggers

Greg K's picture

He has: 2,115 posts

Joined: Nov 2003

This probably is for most SQL servers, but I am using on mySQL 5.x:

To clarify, I know there are many ways to achieve this, but in my case the following is what we need to happen:

Creating a trigger that when a record is inserted, it sets another field in the same row to have it's auto-incremented id as part of the value

I've come very close, trying a AFTER INSERT trigger, however you cannot do an UPDATE on the same table whose trigger is firing.

CREATE TRIGGER ai_SetURL AFTER INSERT ON tblEvent
FOR EACH ROW UPDATE tblEvent SET EventURL=CONCAT("whatever.php?event=",EventID)

Anyone know of a way to do this?

Thanks.

-Greg

This space intentionally left blank...

greg's picture

He has: 1,573 posts

Joined: Nov 2005

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.

Greg K's picture

He has: 2,115 posts

Joined: Nov 2003

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 Wink

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

This space intentionally left blank...

greg's picture

He has: 1,573 posts

Joined: Nov 2005

Greg K wrote:
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 ;-)
Hmm, you would think so yes.
My quote is from MYSQL.com, and it doesn't specify alternative query types are acceptable, but I know what you're saying ...

pr0gr4mm3r's picture

He has: 1,507 posts

Joined: Sep 2006

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.

greg's picture

He has: 1,573 posts

Joined: Nov 2005

That defeats the client being able to edit the field with the VALUE and ID already merged

pr0gr4mm3r's picture

He has: 1,507 posts

Joined: Sep 2006

Wouldn't that need the row to have already been created (inserted) to obtain the ID.

Yes - insert the data normally, and then concat that extra bit of data when you select that row from the DB later.

greg's picture

He has: 1,573 posts

Joined: Nov 2005

Who said that? Sticking out tongue

I changed my post (same time as you where writing) to give the reason why it's not what he wants.
But dammit you must have been pressing refresh until someone replied ! Laugh

They have: 121 posts

Joined: Dec 2008

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

greg's picture

He has: 1,573 posts

Joined: Nov 2005

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

Logically yes, but seemingly you cannot update the same table that invoked the trigger, regardless of difference between query and trigger event type (possibly something to do with the MYSQL table lock- dunno).

It returns an error though, and there are bugs reported (and people venting their frustration) about this around the net, including on dev.mysql.com.

There are ways around certain requirements by using a BEFORE trigger.
But as trying to get next value of a MYSQL auto inc is a bad idea, Greg needs to do the actual insert first to obtain the auto increment ID to append to the 'VALUE' on UPDATE, using BEFORE wont work for him.

(also, obviously there would be no row to update to before the insert)

Link to an example

They have: 9 posts

Joined: May 2013

CREATE TRIGGER ai_SetURL AFTER INSERT ON tblEvent
FOR EACH ROW UPDATE tblEvent SET EventURL=CONCAT("whatever.php?event=",EventID)

teammatt3's picture

He has: 2,102 posts

Joined: Sep 2003

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.

He has: 8 posts

Joined: May 2013

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.

They have: 3 posts

Joined: Feb 2013

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.

Visite our site Semaphore Softwares for magento customization
Email : info@silvertouch.com Phone : +1 201 299 3529
497 Route 27, Iselin, NJ 08830 United States

They have: 32 posts

Joined: Nov 2013

thanks for info

rosyhyden's picture

They have: 3 posts

Joined: Nov 2013

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

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.