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

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.