cbridH2p

1CREATE TABLE superadmin_constraint_lock_table();
2
3CREATE OR REPLACE FUNCTION check_at_least_one_active_superadmin()
4RETURNS TRIGGER AS
5$$
6BEGIN
7	IF current_setting('transaction_isolation') <> 'read committed' THEN
8		RAISE EXCEPTION 'Updating user_groups or user_sites must be done in READ COMMITTED MODE.'; 
9	END IF;
10	LOCK TABLE superadmin_constraint_lock_table IN ACCESS EXCLUSIVE MODE;
11	-- Here I make sure that the changes to the tables will leave at least one active superadmin left.
12	IF NOT EXISTS (SELECT * FROM user_groups AS ug JOIN groups AS g ON ug.group_id = g.group_id JOIN user_sites AS us ON us.user_id = ug.user_id) THEN
13		RAISE EXCEPTION 'There must be one active superadmin at all times.';
14	END IF;
15END;
16RETURN NEW;
17$$ LANGUAGE plpgsql;
18
19
20CREATE TRIGGER trg_user_sites_at_least_one_active_superadmin AFTER DELETE OR UPDATE ON user_sites FOR EACH ROW EXECUTE check_at_least_one_active_superadmin();
21CREATE TRIGGER trg_user_groups_at_least_one_active_superadmin AFTER DELETE ON user_groups FOR EACH ROW EXECUTE check_at_least_one_active_superadmin();
22
23
24DROP TRIGGER trg_user_groups_at_least_one_active_superadmin;
25DROP TRIGGER trg_user_sites_at_least_one_active_superadmin;
26DROP FUNCTION check_at_least_one_active_superadmin;
27DROP TABLE superadmin_constraint_lock_table;