I am working on a small modular system where the users who work in the company can also be consumers or end users of the application, but in this case, those employees stop working in that company, but they are not deprived of using the software, they only lose those administration accesses.
The administration records are not deleted until after a year, so the records stay there in case the employee returns, so the deletion should only happen once a year.
Each user who registers in the application has a record in the table Profiles
and the field HasAdminRights
set to zero / false
, but if this same user starts working in the company (remote case, not impossible) , then an entry is created in the table AdminRights
and set HasAdminRights
to 1/ true
to access the system administrative tools.
These are the possible meanings of the values of both fields:
- In a normal case,
HasAdminRights
it should be 1 andRevoked
it should be 0. - If an employee stops working in the company,
HasAdminRights
it becomes 0 andRevoked
is 1.
This is the schema I have from the database:
--
-- Customer profile table.
--
create table Profiles (
[ID] int
identity(1,1) primary key not null,
-- ... Algunos campos ...
[DisabledAdminRights] bit not null default 1,
[RegisterDate] datetime not null,
[LastLoginDate] datetime not null
);
--
-- Administrator rights.
--
create table AdminRights (
[ID] int
identity(1,1) primary key not null,
[ProfileID] int not null unique,
-- ... Otros campos ...
[AccessGrant] datetime not null,
[Revoked] bit not null default 0,
constraint fkProfID4EmpRights foreign key ([ProfileID]) references Profiles(ID)
on update cascade on delete cascade -- No idea whether this is good or not.
);
In short, what I need to know is how to change the value of the field [Revoked]
in the table AdminRights
when the table field DisabledAdminRights
is Profiles
changed to 1
or true
?
I have tried a Stored Procedure:
create procedure SetRevoked
@ProfID
as
begin
update AdminRights set Revoked=1 where ProfileID = @ProfID
end
But I don't know how to make it run every time the user makes that change in his profile.