aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_tr_followup_notificattion
Parameters
Name
Type
Mode
Definition
DECLARE alert_user text[] := new.assign_users; i integer; i_notification_id integer; BEGIN CASE TG_OP when 'INSERT' then if ((alert_user is not null) and (array_length(alert_user, 1) > 0)) then FOR i IN 1 .. array_upper(alert_user, 1) LOOP -- Add notification insert into notification(notify_type, notify_date, notify_user) values ('followup', new.create_time, alert_user[i]) returning id into i_notification_id; -- Add notification_followup insert into notification_followup(notification_id, followup_id) values (i_notification_id, new.id); -- Set queue to realtime notify perform sp_set_queue_realtime_notify(alert_user[i]); END LOOP; end if; when 'UPDATE' then -- Watching by Trigger in followup table -- Watch column assign_users -- Renew insert every change column assign_users --------------------------------------------------------------------- -- Delete all table that have relationship of followup about notification -- -- [This delete like action when 'DELETE'] -- Delete notification_followup with notification_followup_deleted as ( delete from notification_followup where followup_id = old.id returning notification_id ) -- Delete notification delete from notification where id in (select notification_id from notification_followup_deleted); --------------------------------------------------------------------- -- Insert new data that changing -- --[This insert like action when 'INSERT'] -- Check assign_users if ((alert_user is not null) and (array_length(alert_user, 1) > 0)) then FOR i IN 1 .. array_upper(alert_user, 1) LOOP -- Add notification insert into notification(notify_type, notify_date, notify_user) values ('followup', current_timestamp, alert_user[i]) returning id into i_notification_id; -- Add notification_followup insert into notification_followup(notification_id, followup_id) values (i_notification_id, new.id); END LOOP; end if; when 'DELETE' then -- Delete notification_followup with notification_followup_deleted as ( delete from notification_followup where followup_id = old.id returning notification_id ) -- Delete notification delete from notification where id in (select notification_id from notification_followup_deleted); end case; return null; END;