I would like to create a conditional update on an audit table, such that if the update on the main table succeeds, then the update on the audit table is performed.
Right now I have this
CREATE DEFINER=root
@localhost
PROCEDURE updateParent
(
IN inId INT(10),
IN inName varchar(75),
IN inEmail varchar(50),
IN inPhone varchar(8),
IN inUsername varchar(50)
)
BEGIN
SET AUTOCOMMIT = 0;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
update parent
set nameOfPerson = inName
,email = inEmail
,phonenr = inPhone
where id = inId;
COMMIT;
SET @rowcount = ROW_COUNT();
IF @rowcount > 0 THEN
INSERT INTO parent_h
(parent_id
, nameOfPerson
, email
, phonenr
, opts
, event
, username
)
SELECT id, nameOfPerson, email, phonenr, ts, "U", inUsername
from parent
where id = inId;
END IF;
COMMIT;
END
Occasionally, if the update is performed and zero rows are updated (no data was changed), then I do not want the audit table update to be performed.
Are there any disadvantages of doing it this way? and are there any better ways? This is a web-application.
UPDATE:
I tried with an AFTER_UPDATE trigger. It seems like MySQL runs the trigger even if zero rows are updated on the table. That means I need to test each field to determine if any actual changes were made.
CREATE DEFINER=root
@localhost
TRIGGER parent_AFTER_UPDATE
AFTER UPDATE ON parent
FOR EACH ROW BEGIN
IF (
OLD.id NEW.id or
OLD.nameOfPerson NEW.nameOfPerson or
OLD.email NEW.email or
OLD.phonenr NEW.phonenr
)
THEN
INSERT INTO parent_h
(
parent_id
,
nameOfPerson
,
email
,
phonenr
,
opts
,
event
,
username
)
VALUES
(
NEW.id,
NEW.nameOfPerson,
NEW.email,
NEW.phonenr,
NEW.ts,
'U',
inUsername
)
;
END IF;
END
An additional issue is that I need to add the userid of the principal performing the update, which is not available in the table update.
Asked by tcelvis
(1 rep)
Jun 16, 2024, 05:28 PM
Last activity: Jul 7, 2025, 07:12 AM
Last activity: Jul 7, 2025, 07:12 AM