CODEWITHSUNDEEP
sqloracle-databasetriggerslimit
Tiago Madeira
Tiago Madeira

Reputation: 3

SQL trigger to limit the number of users

I'm still learning how to use SQL, and I need some help creating this database.

I've created the following tables:

create table Worker
(Num_Worker number(20) PRIMARY KEY,
Name varchar2(30),
ID number(8),
Password varchar2(20));

create table Area
(Code_Area number(10) PRIMARY KEY,
Name varchar2(30),
Current_workers number(2)
Max_workers number(2));

create table Timetable
(ID_Timetable number(10) PRIMARY KEY, 
Time_Entrance date,
Time_Exit date,
Code_Area number(10),
Num_Worker number(20),
Foreign Key (Num_Worker) references Worker(Num_Worker),
Foreign Key (Code_Area) references Area(Code_Area));

Supposedly, each worker can choose a area to work at, but each area has a limit or workers at the same time.

What would happen is a worker would create a new timetable, but before that timetable is created it should check the area that he chose to check if the "Current_workers" is the same value as "Max_workers", and if it is it shouldn't let it happen.

I've been trying to create a trigger for that, but I've had no luck in finding the correct syntax for it, and I'm not sure how to do it either, or if there's a better way to do it than with a trigger. This is all I've done so far:

create trigger limit_worker_per_zone
before insert
on Timetable
for each row
as
 BEGIN
  if (select Current_Workers from Area) >= (select Max_workers from Area) <-Not sure...
  BEGIN
    ???
  END
 END

I'd really appreciate if you can help me in this. I'll still be looking for more info myself meanwhile, but the more help the better.

Upvotes: 0

Views: 1829

Answers (2)

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

CREATE TRIGGER limit_worker_per_zone
BEFORE INSERT
ON Timetable
FOR EACH ROW
DECLARE
 v_total NUMBER;
 BEGIN
   BEGIN 
    SELECT max_workers - current_workers
    INTO v_total
    FROM Area
    WHERE Code_Area = :NEW.Code_Area;
    exception when no_data_found then
       v_total := 1;
    END;
 IF v_total <= 0 THEN
   raise_application_error(990000, 'workers full for this area');
 END IF;
END limit_worker_per_zone;

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Try this as a starting point:

CREATE OR REPLACE TRIGGER LIMIT_WORKER_PER_ZONE
  BEFORE INSERT ON TIMETABLE
  FOR EACH ROW
AS
  nCurrent_workers  NUMBER;
  nMax_workers      NUMBER;
BEGIN
  SELECT CURRENT_WORKERS,
         MAX_WORKERS
    INTO nCurrent_workers,
         nMax_workers
    FROM AREA
    WHERE CODE_AREA = :NEW.CODE_AREA;

  IF nCurrent_workers = nMax_workers THEN
    NULL;  -- add code here to do the appropriate thing when the area is already full
  ELSE
    NULL;  -- add code here to to the appropriate thing if the area is not full
  END IF;
END LIMIT_WORKER_PER_ZONE;

I'll leave it to you to flesh this out.

Share and enjoy.

Upvotes: 0

Related Questions