• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Alternative to trigger - back end mysql (1 Viewer)

DKoehne

Registered User.
Local time
Yesterday, 19:56
Joined
Apr 10, 2017
Messages
49
UPDATE usermanagement SET usermanagement.tblClients_fldDRCClientID =
(
SELECT MAX(fldDRCClientID) AS MaxOffldDRCClientID
FROM tblClients
)
ORDER BY id DESC
limit 1;



the query above works but I have an event related issue. T1 triggers to T2 and I can't place this on T2 (as a new trigger) it conflicts with the T1 trigger and throws an error (not sure why). Any thoughts on where else it could go as a process related to passing the MaxOffldDRCClientID back to T1 after T2 record insert (I have tried both before and after insert and they both throw the same error saying it conflicts with the T1 trigger)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 03:56
Joined
Feb 19, 2013
Messages
12,528
not sure why you need the order by or limit - max is max. There is also the Last_Insert_ID function.

This is an Access forum, there will be a few who are familiar with MySQL but I would think your question would be better directed to a MySQL forum. Regret I can't offer any more advice than that
 

aaronkempf

New member
Local time
Yesterday, 19:56
Joined
Jul 7, 2020
Messages
14
if you were talking about a MSSQL database, then I would start by creating these two indexes for this query:

UPDATE usermanagement SET usermanagement.tblClients_fldDRCClientID =
(
SELECT MAX(fldDRCClientID) AS MaxOffldDRCClientID
FROM tblClients
)
ORDER BY id DESC
limit 1;

CREATE INDEX IX_usermanagement_id_desc
on usermanagement(id DESC)

and

CREATE INDEX IX_tblClients_fldDRCClienntID
on tblClients(fldDRCClientID)

I'm not positive of the EXACT syntax on the mysql side, but that is the first thing that I would do if you're having performance issues at ALL.
 

Users who are viewing this thread

Top Bottom