MySQL Forums :: Newbie :: Audit Trail Help


Advanced Search

Audit Trail Help
Posted by: Kevin Bleeker ()
Date: March 08, 2009 07:49AM

Hi all,

I admit I'm terribly new to MySQL, and I want to find a way to log changes from one table(patientstocklevel) to another(patientstocklevel_backup). I've been trying triggers:

CREATE
DEFINER = current_user
TRIGGER TRGPatientStockInsert after insert
ON patientstocklevel FOR EACH ROW
insert into cddb.patientstocklevel_backup(fkpatientid, fkstockid, stocklevel, currentdose, prescriptiondate, prescriptionrenew, logtime)
select fkpatientid, fkstockid, stocklevel, currentdose, prescriptiondate, prescriptionrenew, (cast(now() as datetime)) from patientstocklevel


and:

CREATE
DEFINER = user
TRIGGER TRGPatientStockChange BEFORE UPDATE
ON patientstocklevel FOR EACH ROW BEGIN
insert into cddb.patientstocklevel_backup(fkpatientid, fkstockid, stocklevel, currentdose, prescriptiondate, prescriptionrenew, logtime)
select fkpatientid, fkstockid, stocklevel, currentdose, prescriptiondate, prescriptionrenew, (cast(now() as datetime)) from patientstocklevel

but as I expected, I get hundreds of duplicate rows. I need to log only the row being updated or inserted. I know in MSSQL there's a temporary table used for the row(s) being updated. Is there such a thing in MySQL?

Otherwise, is there another way to only update the rows affected by an update/insert statement?

Thanks, in advance



Edited 1 time(s). Last edit at 03/08/2009 11:54AM by Kevin Bleeker.

Options: ReplyQuote


Subject Written By Posted
Audit Trail Help Kevin Bleeker 03/08/2009 07:49AM
Re: Audit Trail Help Peter Brawley 03/08/2009 02:43PM
Re: Audit Trail Help Kevin Bleeker 03/09/2009 06:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.