Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Evergreen Old and New Item Alert Sync
-- Copy old asset.copy(alert_message) to asset.copy_alert and vice versa
-- For situations when some staff are still using xul and some have moved to web staff client.
CREATE FUNCTION larl_copy_old_new_alert() RETURNS trigger AS $larl_copy_old_new_alert$
BEGIN
-- alert_message is now null - mark matching asset.copy_alert as clear
if NEW.alert_message is null then
update asset.copy_alert
set ack_time = now()
where alert_type=1
and copy=NEW.id
and note=OLD.alert_message
and ack_time is null;
return NEW;
end if;
-- alert_message is blank - it was cleared - mark matching asset.copy_alert as cleared
if NEW.alert_message = '' then
update asset.copy_alert
set ack_time = now()
where alert_type=1
and copy=NEW.id
and note=OLD.alert_message
and ack_time is null;
return NEW;
end if;
-- alert message was null or blank - add a new alert ot asset.copy_alert
if (OLD.alert_message = '' or OLD.alert_message is NULL) then
insert into asset.copy_alert (alert_type, copy, temp, create_time, create_staff, note)
values (1, NEW.id, false, now(), 1, NEW.alert_message)
;
return NEW;
end if;
-- alert_message was updated -- Update matching asset.copy_alert
if NEW.alert_message != OLD.alert_message then
update asset.copy_alert
set note = NEW.alert_message
where alert_type=1
and copy=NEW.id
and note=OLD.alert_message
and ack_time is null;
return NEW;
end if;
END;
$larl_copy_old_new_alert$ LANGUAGE plpgsql;
drop trigger larl_copy_old_new_alert_trigger on asset.copy;
CREATE TRIGGER larl_copy_old_new_alert_trigger
AFTER UPDATE ON asset.copy
FOR EACH ROW
WHEN (OLD.alert_message is distinct from NEW.alert_message and pg_trigger_depth() = 0)
EXECUTE PROCEDURE larl_copy_old_new_alert();
CREATE or replace FUNCTION larl_copy_new_old_alert() RETURNS trigger AS $larl_copy_new_old_alert$
BEGIN
if TG_OP = 'UPDATE' then
-- alert cleared - delete matching asset.copy alert_message.
if OLD.ack_time is null and NEW.ack_time is not null then
update asset.copy
set alert_message=NULL
where
id=NEW.copy
and OLD.note=alert_message;
return NEW;
end if;
-- alert note changed - update matching asset.copy alert_message
if OLD.note is distinct from NEW.note then
update asset.copy
set alert_message=NEW.note
where
id=new.copy
and OLD.note=alert_message;
return NEW;
end if;
end if;
-- new alert note - update copy alert message if it doesn't exist.
if TG_OP = 'INSERT' then
update asset.copy
set alert_message=NEW.note
where
id=new.copy
and (alert_message is null or alert_message = '');
return NEW;
end if;
return NEW;
END;
$larl_copy_new_old_alert$ LANGUAGE plpgsql;
drop trigger larl_copy_new_old_alert_trigger on asset.copy_alert;
CREATE TRIGGER larl_copy_new_old_alert_trigger
AFTER UPDATE ON asset.copy_alert
FOR EACH ROW
WHEN ((OLD.note is distinct from NEW.note or OLD.ack_time is distinct from NEW.ack_time) and (pg_trigger_depth() = 0))
EXECUTE PROCEDURE larl_copy_new_old_alert();
drop trigger larl_create_new_old_alert_trigger on asset.copy_alert;
CREATE TRIGGER larl_create_new_old_alert_trigger
after insert on asset.copy_alert
for each row
when (pg_trigger_depth() = 0)
execute procedure larl_copy_new_old_alert();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.