CODEWITHSUNDEEP
sqloracle-databaseselectplsqldatabase-trigger
Gwynbleidd
Gwynbleidd

Reputation: 87

Create a trigger after insert that update another table

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

Upvotes: 1

Views: 100

Answers (2)

Belayer
Belayer

Reputation: 14861

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.

Upvotes: 1

GMB
GMB

Reputation: 222462

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.

Upvotes: 2

Related Questions