I have two table
user (user_id, username, status )
status --> (1 - active, 0 - passive)
user_work (user_work_id, user_id, status)
status --> (1 - active, 0 - passive)
How set automatically in user table status = 0 if in user_work table don't have active status for some user. If have for some user in user_work active status in user table set status = 1
Is it possible with triger or maybe use some other solution like call some procedure?
From my point of view, that would be a bad idea as you'd keep status information in two tables (so bye-bye normalization). If you have to know someone's status, query it.
Sample tables:
SQL> select * from t_user;
USER_ID USERNAM
---------- -------
1 Little
2 Foot
3 Pointer --> doesn't have STATUS in USER_WORK
SQL> select * from user_work;
USER_WORK_ID USER_ID STATUS
------------ ---------- ----------
100 1 0
101 1 1 --> last status for USER_ID = 1 is 1
102 2 1 --> last (and only) status for USER_ID = 2 is 1
SQL>
Query (presuming that last status (sorted by USER_WORK_ID in descending order is someone's current status; if there's no row in USER_WORK table, then status = 0).
SQL> with temp as
2 (select w.user_id, w.status,
3 row_number() over (partition by w.user_id order by w.user_work_id desc) rn
4 from user_work w
5 )
6 select u.user_id, u.username, nvl(t.status, 0) status
7 from t_user u left join temp t on t.user_id = u.user_id
8 and t.rn = 1;
USER_ID USERNAM STATUS
---------- ------- ----------
1 Little 1
2 Foot 1
3 Pointer 0
SQL>
Or, a simple way, create a view:
SQL> create or replace view v_user_status as
2 with temp as
3 (select w.user_id, w.status,
4 row_number() over (partition by w.user_id order by w.user_work_id desc) rn
5 from user_work w
6 )
7 select u.user_id, u.username, nvl(t.status, 0) status
8 from t_user u left join temp t on t.user_id = u.user_id
9 and t.rn = 1;
View created.
SQL> select * From v_user_status;
USER_ID USERNAM STATUS
---------- ------- ----------
1 Little 1
2 Foot 1
3 Pointer 0
SQL>
I think you can achieve this by statement trigger, something like this should serve the purpose. Of course, you should start from a cleaning point, it means first you need to update all the values in the user table with the latest status of the user_work table.
I believe as well that @Littlefoot statement is correct, keeping the same field in two tables is never a good idea.
What I give you here is a solution to maintain the status in your user table using changes or new entries in the user_work table. I think it is what you asked for.
Let's imagine this scenario ( I used different names for the tables )
SQL> create table user_names ( user_id number, username varchar2(1) , status varchar2(1) ) ;
Table created.
SQL> insert into user_names values ( 1 , 'A' , 1 );
1 row created.
SQL> insert into user_names values ( 2 , 'B' , 1 );
1 row created.
SQL> create table user_work ( user_work_id number, user_id number, status varchar2(1) ) ;
Table created.
In this scenario, I have no rows yet in the user_work table, so let's create the statement trigger to update or insert
SQL> create or replace trigger upd_status_user
after insert or update on user_work
begin
merge into user_names t
using ( select * from user_work ) s
on ( t.user_id = s.user_id )
when matched then
update set t.status = s.status
where
s.user_work_id = ( select max(user_work_id) from user_work s where t.user_id = s.user_id ) ;
end;
/
Trigger created.
SQL>
Now we test it
SQL> insert into user_work values ( 100 , 1 , 1 );
1 row created.
SQL> commit ;
Commit complete.
SQL> select * from user_names ;
USER_ID U S
---------- - -
1 A 1
2 B 1
SQL> insert into user_work values ( 101 , 1 , 0 );
1 row created.
SQL> commit ;
Commit complete.
SQL> select * from user_names ;
USER_ID U S
---------- - -
1 A 0
2 B 1
SQL> insert into user_work values ( 102 , 1 , 1 ) ;
1 row created.
SQL> commit ;
Commit complete.
SQL> select * from user_names ;
USER_ID U S
---------- - -
1 A 1
2 B 1
You can see the changes in user_names table ( your user table ) when I am inserting new records in the user_work table , maintaining the latest status.
If I update, it happens the same
SQL> update user_work set status = 0 where user_work_id=102 ;
1 row updated.
SQL> commit ;
Commit complete.
SQL> select * from user_names ;
USER_ID U S
---------- - -
1 A 0
2 B 1
one option , if time is not tricky you can create a job to check it hourly and set status like this :
BEGIN
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'job_name',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'BEGIN
UPDATE USER
SET STATUS = 0
WHERE USER_ID NOT IN (SELECT USER_ID FROM USER_WORK W WHERE STATUS = 1);
COMMIT;
END;',
START_DATE => SYSTIMESTAMP,
REPEAT_INTERVAL => 'freq=hourly; byminute=0; bysecond=0;',
ENABLED => TRUE);
END;
Note: You can use triggers too, but in my oppinion status should be stored on one place and should directly updated on specific operations when its needed
So, you're kinda provided with almost all possible option except one I believe. If following Littlefoot's answer is not an option for you, you may try to use virtual columns.
Create user_work table:
create table user_work(user_work_id number, user_id number, status number);
Create stored function to calculate status for a user:
create or replace function get_status(p_user_id number) return number deterministic
is
active_cnt number := 0;
begin
select count(1)
into active_cnt
from user_work uw
where uw.user_id = p_user_id;
if active_cnt > 0 then
return 1;
else
return 0;
end if;
end;
Create table user_tab as following:
create table user_tab(user_id number, status generated always as (get_status(user_id)) virtual);
Now, let's test:
insert into user_tab(user_id) values(1);
insert into user_tab(user_id) values(2);
insert into user_work(user_work_id, user_id, status) values(1, 1, 1);
select * from user_tab;
| user_id | status |
|---|---|
| 1 | 1 |
| 2 | 0 |