Create a trigger after insert that update another table

Question

I'm trying to create a trigger that update another table after an insert, when the state of the swab test changes from positive to negative.

I have created this trigger, but the problem is that every time there is a user with a negative swab, the user id is copied to the table, even if this user has never been positive. Maybe, have I to compare date?

Create or replace trigger trigger_healed 
After insert on swab_test
For each row
Begin
  if :new.result = 'Negative' then
     UPDATE illness_update
     SET illness_update.state = 'healed'
     WHERE illness_update.id_user = :new.id_user;
 end if;
end;

This is the result that I'm trying to get.

SWAB_TEST

id_user id_swab swab_result   date
1       test1   'positive'  May-01-2020
1       test1   'negative'  May-08-2020
2       test2   'negative'  May-02-2020

ILLNESS_UPDATE

id_user  state     date
1       'healed'  May-08-2020

in the update statement, apart from checking for id_user, you might want to search for anything that identifies if a patient is ill indeed — micklesh
– micklesh, Commented May 7, 2020 at 22:48

GMB · Accepted Answer · 2020-05-07 22:50:09Z

2

What you ask for would require the trigger to look at the existing rows in the table that is being inserted on - which by default cannot be done, since a trigger cannot action the table it fires upon.

Instead of trying to work around that, I would suggest simply creating a view to generate the result that you want. This gives you an always up-to-date perspective at your data without any maintenance cost:

create view illness_update_view(id_user, state, date) as
select id_user, 'healed', date
from (
    select 
        s.*, 
        lag(swab_result) over(partition by id_user order by date) lag_swab_result
    from swab_test s
) s
where lag_swab_result = 'positive' and swab_result = 'negative'

The view uses window function lag() to recover the "previous" result of each row (per user). Rows that represents transitions from a positive to a negative result are retained.

edited May 7, 2020 at 22:50

answered May 7, 2020 at 22:44

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gwynbleidd Over a year ago

Thanks for the help, sorry if I ask useless questions but I recently started studying SQL, with this view, I always need the trigger I created right? Because without it, the view has nowhere to get the data, I'm wrong?

halfer · Accepted Answer · 2020-06-04 09:35:57Z

As @GMB indicates you cannot do what you are asking with a standard before/after row trigger as it that cannot reference swab_test as that is the table causing the trigger to fire (that would result in an ORA-04091 mutating table error). But you can do this in a Compound Trigger (or an After statement). But before getting to that I think your data model has a fatal flaw.

You have established the capability for multiple swab tests. A logical extension for this being that each id_swab tests for a different condition, or a different test for the same condition. However, the test (id_swab) is not in your illness update table. This means if any test goes to negative result after having a prior positive result the user is healed from ALL tests. To correct this you need to a include id_swab id making the healed determination. Since GMB offers the best solution I'll expand upon that. First drop the table Illness_update. Then create Illness_update as a view. (NOTE: in answer to your question you DO NOT need a trigger for the view, everything necessary is in the swab_test; see lag windowed function.

create view illness_update(id_user, state, swab_date) as
select id_user,  id_swab, 'healed' state,swab_date
from (
    select 
        s.* 
     ,  lag(swab_result) over(partition by id_user, id_swab
                             order by id_user, id_swab, swab_date) as lag_swab_result
    from swab_test s 
) s
where lag_swab_result = 'positive'
  and swab_result = 'negative';

Now, as mentioned above, if your assignment requires the use of a trigger then see fiddle. Note: I do not use date (or any data type) as a column name. Here I use swab_date in all instances.

Collectives™ on Stack Overflow

Create a trigger after insert that update another table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related