Master Help: How to trigger the main table according to the content of the subtable?

zzxcv44614

io_works main table
wid isFinish finishDate
1 0 null
2 0 null

io_workdes subtable
id wid isok
1 1 0
2 1 0
3 1 1

How to use triggers to achieve the following functions:
When the isok of the child table becomes 1, check whether the isok of the other child records (according to the wid) are all 1. If it is 1, modify the isFinish of the main table to 1, otherwise it is unchanged.

create or replace trigger TR_IO_WORKDES_UPDATE
after UPDATE on IO_WORKDES
for each row
declare
begin
UPDATE IO_WORKS
SET ISFINISH = '1', FINISHDATE = TO_CHAR (SYSDATE, 'YYYY-MM-DD')
WHERE WID =: OLD.WID;
--AND NOT exists (SELECT ID FROM IO_WORKDES WHERE WID =: OLD.WID AND ISOK = '0')

end TR_IO_WORKDES_UPDATE;
I wrote this problem with mutation table, thank you! !!

whbilly

Check whether the isok of other sub-records (according to the wid) is all 1, if it is 1, modify the isFinish of the main table to 1, otherwise it will not change.

Need to add if judgment conditions

whbilly

such as:
if: NEW.isok = 1 then
    UPDATE IO_WORKS
      SET ISFINISH = '1', FINISHDATE = TO_CHAR (SYSDATE, 'YYYY-MM-DD')
      WHERE WID =: OLD.WID;
elsif: NEW.isok = 0 then
        null;
end if;

yuanin

When the isok of the sub-table becomes 1, check whether the isok of other sub-records (according to the wid) are all 1, if it is 1.
? ? ?
That is to say, when the corresponding wid is the same, and the isok is all 1, only then modify the main table?
If this is the case, I can't imagine a solution that can be solved using triggers. Unless after modifying the main table, use the modification conditions to modify the main table.

zzxcv44614

Top
yuanin


   
When the isok of the sub-table becomes 1, check whether the isok of other sub-records (according to the wid) is all 1, if it is 1.
? ? ?
That is to say, when the corresponding wids are the same, and the isok is all 1 to modify the main table?
If this is the case, I can't imagine a solution that can be solved using triggers. Unless after modifying the main table, use the modification conditions to modify the main table.

  


Understand correctly. Fear of incomplete modification in the program, the best trigger.

yuanin

The modification in the program must be thorough (unless you make a programming error). The trigger should not be possible.

dewind

It is easy to implement, the code is as follows:
create or replace trigger tri_io_works
after update or insert on io_works_son
declare
       cursor c is select wid,sum_isok from
       (select wid,sum(nvl(isok,0))/count(nvl(isok,0)) as sum_isok from io_works_son group by wid)
               where sum_isok =1;
       v_wid io_works_son.wid%TYPE;
       v_isok io_works_son.isok%TYPE;

begin
     open c;
     loop
         fetch c into v_wid,v_isok;
         exit when c%notfound;
         update io_works set isfinish =1 where wid = v_wid;
     end loop;
end;

背空悯宇

If for row-level triggers
before/* or after*/ UPDATE on IO_WORKDES
for each row

if :new.ISOK <> :old.ISOK then
if :new.ISOK='1' then
  if NOT exists (SELECT ID FROM IO_WORKDES
    WHERE WID=:OLD.WID AND ISOK='0' AND ID<>:old/* or new*/.ID) then
   UPDATE IO_WORKS
   ...

If there are problems with error protection and mutation table,

You can use package variables and statement-level triggers:
Define a package variable type as an index table, or an array
Trigger at the row level
if :old.ISOK='1' and :new.ISOK ='0' then
widchgs(v_wid)='0';
elseif :old.ISOK='0' and :new.ISOK ='1' then
if not widchgs.EXISTS(v_wid) then
   widchgs(v_wid)='1';
end if;
end if;

Triggers at the after statement level
Can cycle UPDATE IO_WORKS

i := widchgs.FIRST;
WHILE i IS NOT NULL LOOP
...widchgs(i)
i := widchgs.NEXT(i);
END LOOP;

背空悯宇

Package variable pkgwid.widchgs

In the after statement level trigger loop:

if pkgwid.widchgs(i) ='1' then
  if NOT exists (SELECT ID FROM IO_WORKDES WHERE WID=i AND ISOK='0') then
   UPDATE IO_WORKS
....