Can't register new employees in hotels

[malsadev]

When

cursor.execute(
                        "INSERT INTO employee (fullname, adress_id, ssn, works_in) VALUES (%s, %s, %s, %s)",
                        [fullname, address_id, ssn, hotel_id]
                    )

I see:

Attempting to assign more than one manager to hotel 806.
CONTEXT:  PL/pgSQL function ensure_unique_manager() line 16 at RAISE

Looking at:

CREATE OR REPLACE FUNCTION ensure_unique_manager() RETURNS TRIGGER AS 
$$
BEGIN
    
    IF (
        SELECT NOT EXISTS (
            SELECT employee_id, role
            FROM 
            employee JOIN employee_roles ON employee.id = employee_id
            WHERE employee.works_in = NEW.works_in AND role = 'manager'::employee_role
        )
       )
    THEN
        RETURN NEW;
    END IF;

    RAISE EXCEPTION 'Attempting to assign more than one manager to hotel %.', NEW.id;
    RETURN NULL;
END;

Seems like there is no reference to a hotel in the logic.

disabling trigger for now.

[malsadev]

@ghaitben

[ghaitben]

Yes, the logic was wrong.
The trigger should apply to the employee_roles table (that's where the role information resides) and not to employee.
This commit seems to fix the issue. The trigger is now called every time the employee_roles table is modified

Let's keep this issue open just in case any further related issues emerge.