unsure of best practice

[benjamenus]

I have two tables, (simplified) structure as below

tblClient
Client -- unique index
AL_User -- can be null
WC_User -- can be null
GL_User -- can be null

tblClaim
ClaimID -- unique index
Client
Code -- always AL, GL or WC
User

they link as follows

tblClient.Client=tblClaim.Client

What I would like to do is this ...

When someone updates a record in tblClaim, I want tblClaim.User to be updated with the appropriate user from tblClient (but only if the user in tblClient is not null). For example, if the tblClaim.Code='AL' then tblClaim.User=tblClient.AL_User (so long as AL_User is not null).

I'm not sure of the best way to achieve this. It would be quite eay to do with a weekly update and some simple SQL, but this isn't really practical due to the number or records involved and also the time lag. I don't believe I can just use an After Update trigger on tblClaim as I can't update the record I'm referencing (is this correct?). So, one idea is to use an After Update trigger to test the values and if an update is required, call a stored proc to update that record in tblClaim (will this work?).

Any ideas/advice very welcome

Cheers

 

[sbentz]

haven't tested this, but it seems this will work.

create or replace trigger bu_tblClaim
before update on tblClaim
for each row
declare
invalid_code exception;
tblClient_row tblClient%rowtype;
begin
select * into tblClient_row
from tblClient
where tblClient.Client = :new.Client;
--
if :new.Code = 'AL'
then :new.User := nvl ( tblClient.AL_User, :new.User );
elsif :new.Code = 'WC'
then :new.User := nvl ( tblClient.WC_User, :new.User );
elsif :new.Code = 'GL'
then :new.User := nvl ( tblClient.GL_User, :new.User );
else raise invalid_code;
end if;
--
exception
when invalid_code
then <do whatever you want here>;
end;

hope this is helpful.

 

[benjamenus]

thanks for the suggestion - i may not be able to test it for a while, but i will let you know if it is successful

cheers

 

[benjamenus]

SBENTZ

had to modify your code a little - but essentially it worked a treat. Many thanks!