Automatic update of employee/store statistics after an employee is hired

Description

This trigger automatically creates the necessary employee/store work record when a new employee is added to the system.

When an employee is hired, the database can automatically associate the employee with the relevant store and initialize their work statistics.

This reduces the need for the application to perform additional database operations after creating an employee.

Tables involved

• Employees
• Personal
• works_in_store

Type of trigger

• AFTER INSERT

Activated on the Employees table.

SQL Code

CREATE OR REPLACE FUNCTION initialize_employee_statistics() 
RETURNS TRIGGER 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
    /* 
    * The employee is created first. 
    * Work statistics can be initialized when 
    * the employee is assigned to a store.  
    * The actual store assignment should be handled 
    * through works_in_store. */ 
    INSERT INTO worked ( 
        employee_id, 
        total_working_hours, 
        total_wage 
    ) 
    VALUES ( 
        NEW.id, 
        0, 
        0 
    ); 
    RETURN NEW; 
END; 
$$; 

CREATE TRIGGER trg_initialize_employee_statistics 
AFTER INSERT 
ON employees 
FOR EACH ROW 
EXECUTE FUNCTION initialize_employee_statistics();

Logic explanation

After a new employee is inserted, the trigger:

1. Identifies the newly created employee using NEW.id. 2. Creates an initial statistics record for the employee. 3. Sets the initial working hours to 0. 4. Sets the initial total wage to 0. 5. Allows the employee creation operation to complete.

The employee's statistics can subsequently be updated through the worked relationship as working hours and wages are recorded.

Reason for trigger

This trigger is useful because:

• It automatically initializes employee statistics.
• It avoids requiring the application to create a second record manually.
• It ensures that every employee has an initial statistics record.
• It provides a consistent starting point for tracking working hours and wages.
• It centralizes employee initialization logic in the database.