Changes between Initial Version and Version 1 of Trigger8

Timestamp:

08/21/26 08:22:02 (27 hours ago)

Author:

235018

Comment:

--

Trigger8

@@ +1 @@
+= 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
+{{{#!sql
+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.