Audit Trail Help

[Kevin Bleeker]

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.