Increment/decrement count value in a table depending on insert/delete of a specific column value of-CodePudding

Tables

The above screenshot shows two tables employee and department where employee contains details of employees and department contains information on the department.

Upon insert or delete in the employee table, depending on the dno value, an increment/decrement has to be done in dcount of the department table.

Goal is to automate the count of employees in department upon insertion and deletion in employee table.

I tried using function which increments/decrements the value using if and elsif for operation 'INSERT' and 'DELETE', but all that does is increment all the values in dcount by 1.

It does not increase/decrease a particular department dcount value respective to inserted or deleted dno value from employee table.

Need some help and guidance.

Thank you.

Gruheeth

CodePudding user response：

tables for test

create table employee (fname text, dno integer);
create table department (dname text, dnumber integer, dcount integer default 0);

insert into department (dname,dnumber) values ('Research',5),('Administration',4),('Headquarters',1);

i created two functions which’s increment and decrement counters in department table and then added them to triggers on delete and insert

CREATE or replace FUNCTION increment_c() RETURNS trigger AS $increment_c$
    BEGIN
        update department set dcount=dcount 1 where dnumber=NEW.dno;
        RETURN NULL;
    END;
$increment_c$ LANGUAGE plpgsql;

CREATE or replace FUNCTION decrement_c() RETURNS trigger AS $decrement_c$
    BEGIN
        update department set dcount=dcount-1 where dnumber=OLD.dno;
        RETURN NULL;
    END;
$decrement_c$ LANGUAGE plpgsql;

create TRIGGER tr_insert AFTER insert on employee for each row execute procedure increment_c();
create TRIGGER tr_delete AFTER delete on employee for each row execute procedure decrement_c();

test and result insert

insert into employee values ('James',1),('Jhon',5),('Frenklin',5);
INSERT 0 3
postgres=# select * from department;
     dname      | dnumber | dcount
---------------- --------- --------
 Administration |       4 |      0
 Headquarters   |       1 |      1
 Research       |       5 |      2

and delete

delete from employee where fname='Jhon';
DELETE 1
postgres=# select * from department;
     dname      | dnumber | dcount
---------------- --------- --------
 Administration |       4 |      0
 Headquarters   |       1 |      1
 Research       |       5 |      1