Changes between Initial Version and Version 1 of Trigger7

Timestamp:

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

Author:

235018

Comment:

--

Trigger7

@@ +1 @@
+= Validation of employee authorization before making a product change
+
+=== Description
+This trigger verifies that a member of the personnel has the required authorization before they are allowed to make a change to a product.
+
+The makes_change relationship contains information about the permission, type, and authorization associated with a product change. The trigger compares the authorization provided for the change with the authorization of the employee recorded in PERSONAL.
+
+If the employee does not have the required authorization, the change is rejected.
+
+==== Tables involved
+- Personal
+- Change
+- makes_change
+
+==== Type of trigger
+- BEFORE INSERT
+- BEFORE UPDATE
+
+Activated on the `makes_change` table.
+
+==== SQL Code
+{{{#!sql
+CREATE OR REPLACE FUNCTION validate_employee_authorization() 
+RETURNS TRIGGER 
+LANGUAGE plpgsql 
+AS $$ 
+DECLARE v_authorisation TEXT; 
+BEGIN 
+    -- Get the employee's authorization 
+    SELECT authorisation 
+    INTO v_authorisation 
+    FROM personal 
+    WHERE id = NEW.id; 
+    -- Check whether the employee exists 
+    IF v_authorisation IS NULL THEN 
+        RAISE EXCEPTION 'Employee % does not have valid authorization.', NEW.id; 
+    END IF; 
+    -- Check whether the authorization matches 
+    IF NEW.authorisation IS DISTINCT FROM v_authorisation THEN 
+        RAISE EXCEPTION 'Employee % is not authorized to make this product change.', NEW.id; 
+    END IF; 
+    RETURN NEW; 
+END; 
+$$; 
+
+
+CREATE TRIGGER trg_validate_employee_authorization 
+BEFORE INSERT OR UPDATE 
+ON makes_change 
+FOR EACH ROW 
+EXECUTE FUNCTION validate_employee_authorization();
+
+}}}
+
+=== Logic explanation
+
+Before a record is inserted or updated in makes_change, the trigger:
+
+**1.** Identifies the employee using NEW.id.
+**2.** Retrieves the employee's authorization from PERSONAL.
+**3.** Checks whether the employee has valid authorization.
+**4.** Compares it with the authorization specified for the product change.
+**5.** If the authorization matches, the operation continues.
+**6.** If it does not match, the database raises an exception and rejects the change.
+
+=== Reason for trigger
+
+This trigger is useful because:
+
+- It prevents unauthorized personnel from modifying products.
+- It enforces access-control rules at the database level.
+- It protects product information from unauthorized changes.
+- It prevents the application from bypassing authorization rules.
+- It centralizes authorization validation in the database.