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?
CodePudding user response:
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>
CodePudding user response:
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
CodePudding user response:
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
CodePudding user response:
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 |