Rather than have a trigger to store the value, I'd make late_in/early_out virtual columns. These include the expression that calculates the difference between the sign_in/out datetimes and the cut-off values. And are calculated at runtime. So guaranteed to stay in sync with the source time if you update it.

There are various techniques you could use to find the difference. Here's one:

- Get 9am on the date of the sign in by:
- Truncing the value to get the date at midnight
- Add 9 hours to this to have 9am that day
- Subtract this from the sign-in, converting to hours/minutes as needed
- (optional: ignore early sign-ins by returning the greatest of the above expression and zero

Which gives:

create table t (
  sign_in         date,
  late_in_minutes number as (
    greatest ( 
      round ( ( sign_in - ( trunc ( sign_in ) + 9/24 ) ) * 1440 ), 
      0
    )
  )
);

insert into t ( sign_in ) 
  values ( date'2019-04-24' + 8/24 );
insert into t ( sign_in ) 
  values ( date'2019-04-24' + 9/24 );
insert into t ( sign_in ) 
  values ( date'2019-04-24' + 9.1/24 );
insert into t ( sign_in ) 
  values ( date'2019-04-24' + 10/24 );
  
select * from t;

SIGN_IN                LATE_IN_MINUTES   
24-APR-2019 08:00:00                     0 
24-APR-2019 09:00:00                     0 
24-APR-2019 09:06:00                     6 
24-APR-2019 10:00:00                    60