I should create a trigger in Oracle SQL that

1. Add department_amount column to locations table
2. Add comment 'Contains the amount of departments in the location'
3. Create a trigger ,which will update the amount of departments in location every time a row is inserted/deleted from departments.

Tables:

CREATE TABLE departments
(
    department_id   NUMBER(4) PRIMARY KEY,
    department_name VARCHAR2(30) NOT NULL,
    manager_id      NUMBER(6),
    location_id     NUMBER(4)    NOT NULL
);

CREATE TABLE locations
(
    location_id    NUMBER(4) PRIMARY KEY,
    street_address VARCHAR2(40),
    postal_code    VARCHAR2(12),
    city           VARCHAR2(30),
    state_province VARCHAR2(25),
    country_id     CHAR(2) NOT NULL
);

CodePudding user response：

To answer your questions

Add department_amount column to locations table

alter table locations add department_amount number ; 

Add comment 'Contains the amount of departments in the location'

comment on column locations.deparment_amount is 'Contains the amount of departments in the location';

Create a trigger ,which will update the amount of departments in location every time a row is inserted/deleted from departments.

create or replace trigger trg_loc 
after insert or delete on departments 
declare
begin
    merge into locations t 
    using ( select count(department_id) as dpt_amount, location_id as loc_id 
            from departments b 
            group by location_id ) s
    on (t.location_id = s.loc_id) 
    when matched then 
     update set t.department_amount = s.dpt_amount ;
end;
/

You have below a db<>fiddle with data example and the trigger demonstration that updates the department_amount in locations table when you insert or delete a department for each location.

db<>fiddle

CodePudding user response：

I think that this task is so much of a don't-do-that that it serves no purpose. Not even for training.

What you'd have to do is

1. Check whether the column locations.department_amount exists. You can do this by looking into the system view dba_tables (or all_tables or user_tables if appropriate).
2. If that column does not exist, create it via ALTER TABLE. This, however is DDL, not DML, so you must run it dynamically via EXECUTE IMMEDIATE.
3. In case you've just created the column, also create the comment.
4. Now, in that same trigger add or subtract 1 from the department_amount. Thus you guarantee that the very first action (the one that leads to creating the column) already updates it.

Points 1 to 3 only have to happen on statement level, while point 4 should happen on row level. For this reason and in order to avoid problems with other triggers, this trigger should be a compound trigger.

But then, if you can create the trigger in the database, why can't you add the column and its comment, too? Why must you write the trigger such that it has to check whether the column exists? As mentioned, this just makes no sense, so the best thing here is not to do that.