Hi, I have not posted before so apologies if this is not set out the way expected.
I have a primary table tPerson containing personal details. tPerson has relationships with may other tables. tPerson.IdPerson is the primary key. Any time a record in tPerson is updated, a trigger puts the old record into tPersonAudit like this:
INSERT INTO tPersonAudit SELECT * FROM tPerson WHERE IdPerson = OLD.IdPerson;.
Trigger also makes sure IdPerson cannot be changed so we are sure that every record in tPersonAudit is related to correct tPerson record. Trigger also does:
SET NEW.eTime = NOW();
which means tPerson and tPersonAudit combined have a full history of what the records in tPerson have been. I want to query them to get the tPerson record as it looked at some time in the past which means finding the maximum eTime, under a limit for a specific IdPerson. I have solved it with the following procedure but it would be much neater if I could do it with a view as I have many such pairs tables that I want to query together with joins. Any insight would be much appreciated!
Proc is as follows:
-- get the tPerson table as it was at timeStamp
CREATE PROCEDURE `GetPersonData`(IN timeStamp DATETIME)
BEGIN
SELECT
tempPerson.*
FROM
(
SELECT
TEMP_PERSON_TABLE.*
FROM
(
SELECT
tPerson.*
FROM
tPerson
WHERE
eTime <= timeStamp
UNION
SELECT
tPersonAudit.*
FROM
tPersonAudit
WHERE
eTime <= timeStamp
) AS TEMP_PERSON_TABLE
INNER JOIN
(
SELECT
IdPerson, MAX(eTime) AS eTimeMax
FROM
(
SELECT
tPerson.*
FROM
tPerson
WHERE
eTime <= timeStamp
UNION
SELECT
tPersonAudit.*
FROM
tPersonAudit
WHERE
eTime <= timeStamp
) AS UNION_PERSON_TABLE
GROUP BY IdPerson
) AS MAX_PERSON_TABLE
ON
MAX_PERSON_TABLE.eTimeMax = TEMP_PERSON_TABLE.eTime
AND
MAX_PERSON_TABLE.IdPerson = TEMP_PERSON_TABLE.IdPerson
) AS tempPerson
I wouldn't use a trigger for something that a ON UPDATE CURRENT_TIMESTAMP field could do.
You can also use the trigger for AFTER UPDATE instead of an insert trigger. (its cleaner)
I also have no idea why you're inner joining these tables togeather... Is your problem not as easy as this:
Code:
CREATE PROCEDURE `GetPersonData`(IN timeStamp DATETIME, person_ID INT)
BEGIN
SELECT * FROM tPerson
UNION ALL
SELECT * FROM tPersonAudit
WHERE IdPerson = person_ID
AND etime <= timeStamp
ORDER BY etime ASC
END
P.S. You can keep the whitespace if you put the code between [C O D E] [/ C O D E] tags (no spaces).
Reply With Quote
