Data Cleansing Trigger

[TimboA]

I currently have a date field in a table that is being written to from an Access Front End. However, sometimes (and I haven't yet found out why!!) the date is written from the access client machines in DD/MM/YY format, but other times it's in MM/DD/YY format.

I need to create a trigger that will capture those records that are being inserted as MM/DD/YY and switch them back to DD/MM/YY. I've tried the following, but get a mutating table error :-

create or replace trigger tg_completed
AFTER INSERT on t_accepted
for each row

DECLARE

v_UserID VARCHAR2(8);
v_Updated DATE;

BEGIN

IF :new.accepted_id is not null then

update T_ACCEPTED
set CLOSE_DATE = trunc(sysdate)
where accepted_id = :new.accepted_id
and request_id = :new.request_id;

END IF;

END;

Anyone know of a way around this.....

 

[Dagon]

No, it's a lot simpler than that. You just need to do:

:new.close_date := trunc(sysdate);

 

[TimboA]

Thanks Dagon, I think my brain was having a relapse...sledge hammer and nut and all that.

 

[TimboA]

Thanks Dagon, I think my brain was having a relapse...sledge hammer and nut and all that.

PS - I had to change it to a BEFORE Insert to get it to work.