Home > Blockchain >  Change status automatically in oracle table
Change status automatically in oracle table

Time:10-21

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.

  1. Create user_work table:

    create table user_work(user_work_id number, user_id number, status number);
    
  2. 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;
    
  3. 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
  • Related