Re: Help with isql - Moving results from Detail to Master table
>
> We have the need to pass data that has been entered into
> a detail table to be moved to a master table. We are
> not sure if a trigger should be used or what. We don't
> find a lot of help from our manuals on using a trigger.
> Here is the situation:
>
> REPORT (Master Table)
> rep_date_completed
> rep_status_code
>
> REVIEW (Detail of REPORT)
> rev_date_completed
> rev_status_code
>
> A number of reviews are made (normally in series) about
> the report. The number of reviews can vary. A reviewer,
> who is designated as final, will issue a COMPLETE status
> code. However the process may not get that far since a
> status code (like CANCEL) can be issued from any of the
> prior reviewers. We want to pass the status code and the
> date_completed from the appropriate Detail to the Master
> table.
>
> We are mainly interested in the final disposition, but
> if interim results (each reviewer) need to be posted to
> the master in order to make the trigger or whatever work,
> then so be it.
>
> We are limited to isql on the SE. We do not have 4GL.
>
Hi,
Some ideas:
create trigger xxxinsert on review
referencing new as post
for each row when (post.rev_status_code = "COMPLETE" or post.rev_status_code =
"CANCEL") (execute procedure yyy(post.rev_date_completed, post.rev_status_code);
create procedure zzz(rev_date date, status char(10))
insert into report (rep_date_completed, rep_status_code) values
(rev_date, status);
end procedure
;
You'll probably want to add some documention, warnings, debugging to the
SP (mail if you need more help). You'll also need more columns - the
primary keys of both tables :-)
HTH
Richard.
-----------------------------------------------------------------
| _________ | Richard Thomas |
| / /_______| | r.thomas@csl.gov.uk |
| / /__/ | |
| /_/ | TRIGGER happy ;-) |
| | |
-----------------------------------------------------------------
PS I work for the Ministry of Agriculture in the UK - I'd be interested
to know what you're doing with Informix!