'Update a table from another table using triggers

I am new using Triggers in Oracle SQL and I have a simple question. I have 3 tables:

Table 1 COURSES
-IDCourse (Primary Key)
-Name
-SpaceAvaible

Table 2 STUDENTS
-IDStudent (Primary Key)
-lastName
-Name

Table 3 REGISTER
-IdRegister (Primary Key)
-Date
-IDCourse FOREIGN KEY
-IDStudent FOREIGN KEY

My question is how to make a trigger that updates the information of SpaceAvaible in COURSES when someone inserts a new data in REGISTER. I tried but I'm stuck. :c

sqloracletriggers


Solution 1:[1]

I presume something like this; this presumes that courses table is already populated, and that spaceavailable isn't empty. How and where does it get its value, I have no idea - I just think that - if someone registers for that course - there's one space less available.

create or replace trigger trg_aiu_reg
  after insert or update on register
  for each row
begin
  update courses set
    spaceavailable = spaceavailable - 1
    where idcourse = :new.idcourse;
end;
/

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Littlefoot

Related Questions