| |

VerySource

 Forgot password?
 Register
Search
View: 37|Reply: 8

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

[Copy link]

1

Threads

2

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 China

Post time: 2020-3-2 16:00:02
| Show all posts |Read mode
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! !!
Reply

Use magic Report

0

Threads

71

Posts

50.00

Credits

Newbie

Rank: 1

Credits
50.00

 China

Post time: 2020-5-13 14:30:02
| Show all posts
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
Reply

Use magic Report

0

Threads

71

Posts

50.00

Credits

Newbie

Rank: 1

Credits
50.00

 China

Post time: 2020-5-14 08:30:01
| Show all posts
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;
Reply

Use magic Report

0

Threads

6

Posts

6.00

Credits

Newbie

Rank: 1

Credits
6.00

 China

Post time: 2020-6-5 20:45:01
| Show all posts
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.
Reply

Use magic Report

1

Threads

2

Posts

3.00

Credits

Newbie

Rank: 1

Credits
3.00

 United States

 Author| Post time: 2020-7-8 17:15:01
| Show all posts
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.
Reply

Use magic Report

0

Threads

6

Posts

6.00

Credits

Newbie

Rank: 1

Credits
6.00

 China

Post time: 2020-7-23 13:45:02
| Show all posts
The modification in the program must be thorough (unless you make a programming error). The trigger should not be possible.
Reply

Use magic Report

0

Threads

1

Posts

2.00

Credits

Newbie

Rank: 1

Credits
2.00

 China

Post time: 2020-7-30 18:15:01
| Show all posts
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;
Reply

Use magic Report

0

Threads

2

Posts

2.00

Credits

Newbie

Rank: 1

Credits
2.00

 China

Post time: 2020-8-1 19:15:01
| Show all posts
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;
Reply

Use magic Report

0

Threads

2

Posts

2.00

Credits

Newbie

Rank: 1

Credits
2.00

 Unknown

Post time: 2020-8-1 19:45:01
| Show all posts
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
....
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2020|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list