aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_tr_guest_messages_counter
Parameters
Name
Type
Mode
Definition
DECLARE v_register_id int; v_booking_id int; v_guest_id int; BEGIN v_register_id = CASE TG_OP WHEN 'DELETE' THEN OLD.register_id ELSE NEW.register_id END; v_booking_id = CASE TG_OP WHEN 'DELETE' THEN OLD.booking_id ELSE NEW.booking_id END; IF v_register_id IS NOT NULL THEN UPDATE registration SET msg_count = (SELECT count(*) FROM guest_messages WHERE register_id = v_register_id) WHERE id = v_register_id; ELSIF v_booking_id IS NOT NULL THEN UPDATE booking SET msg_count = (SELECT count(*) FROM guest_messages WHERE booking_id = v_booking_id) WHERE id = v_booking_id; END IF; -- Send message to guest IF TG_OP = 'INSERT' THEN v_guest_id = CASE WHEN (NEW.register_id NOTNULL) THEN (SELECT guest_id FROM registration r WHERE r.id = NEW.register_id) ELSE (SELECT guest_id FROM booking b WHERE b.id = NEW.booking_id) END; PERFORM sp_send_message_to_guest(v_guest_id, msg_template => 'guest-message', params => jsonb_build_object('MESSAGE_FROM', NEW.message_from, 'MESSAGE' , NEW.message) ); END IF; RETURN NULL; END