| |

VerySource

 Forgot password?
 Register
Search
View: 16|Reply: 3

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

[Copy link]

1

Threads

1

Posts

2

Credits

Newbie

Rank: 1

Credits
2

 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

66

Posts

45

Credits

Newbie

Rank: 1

Credits
45

 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

66

Posts

45

Credits

Newbie

Rank: 1

Credits
45

 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

4

Posts

4

Credits

Newbie

Rank: 1

Credits
4

 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

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