github.com/diadata-org/diadata@v1.4.593/deployments/config/pgtriggers.sql (about)

     1  CREATE OR REPLACE FUNCTION ensure_mandatory_read_write() 
     2  RETURNS TRIGGER AS $$
     3  BEGIN
     4      IF TG_OP = 'DELETE' AND OLD.access_level = 'read_write' THEN
     5          IF NOT EXISTS (
     6              SELECT 1 
     7              FROM wallet_public_keys 
     8              WHERE customer_id = OLD.customer_id 
     9              AND access_level = 'read_write'
    10              AND key_id != OLD.key_id 
    11          ) THEN
    12              RAISE EXCEPTION 'Customer % must have at least one read_write access.', OLD.customer_id;
    13          END IF;
    14      END IF;
    15  
    16      IF TG_OP = 'UPDATE' AND OLD.access_level = 'read_write' AND NEW.access_level != 'read_write' THEN
    17          IF NOT EXISTS (
    18              SELECT 1 
    19              FROM wallet_public_keys 
    20              WHERE customer_id = OLD.customer_id 
    21              AND access_level = 'read_write'
    22              AND key_id != OLD.key_id
    23          ) THEN
    24              RAISE EXCEPTION 'Customer % must have at least one read_write access.', OLD.customer_id;
    25          END IF;
    26      END IF;
    27  
    28      RETURN NEW;
    29  END;
    30  $$ LANGUAGE plpgsql;
    31  
    32  
    33  CREATE TRIGGER check_delete_read_write
    34  BEFORE DELETE ON wallet_public_keys
    35  FOR EACH ROW
    36  EXECUTE FUNCTION ensure_mandatory_read_write();
    37  
    38  
    39  
    40  CREATE TRIGGER check_update_read_write
    41  BEFORE UPDATE ON wallet_public_keys
    42  FOR EACH ROW
    43  EXECUTE FUNCTION ensure_mandatory_read_write();