Re: Need help with trigger

On 1/23/21 4:57 AM, Condor wrote:
> 
> 
> Hello ppl,
> 
> I need help with trigger how to replace insert command with update. 
> External program read data from one table and in every 30 min I need to 
> refresh data in arhive table.

What is the table being read from and is it in the same database?

See more comments inline below.

> What I want is: if data already inserted and end_date is not changed, 
> only to update lastseen column. If data not exists to insert data and if 
> data exists and end_date is changed
> to update end_date, lastseen and sendto columns. Well, update probably 
> will never happened, this functionality is left for frontend but its can 
> be cut off.
> 

> 
> and my trigger is:
> 
> CREATE OR REPLACE FUNCTION public.log_last_chaged()
>   RETURNS trigger
>   LANGUAGE plpgsql
> AS $function$
> 
> enddate DATE;
> 
> BEGIN
>    IF TG_OP = 'INSERT' THEN
>      SELECT INTO enddate end_date FROM arhive_table WHERE contract = 
> NEW.contract AND service = NEW.service;
>      IF enddate IS NULL THEN
>        -- line below probably will do normal INSERT
>        RETURN NEW;
>      ELSIF enddate IS DISTINCT FROM NEW.end_date THEN
>          NEW.sendto := 0;
>          NEW.uts := date_part('epoch', CURRENT_TIMESTAMP)::integer;
>          -- But here need to do UPDATE not INSERT

So do an UPDATE and the RETURN NULL to cancel the INSERT. Untested example:

UPDATE arhive_table SET sendto = 0, uts = date_part('epoch', 
CURRENT_TIMESTAMP)::integer WHERE contract = NEW.contract AND service = 
NEW.service;

RETURN NULL;


>      END IF;
>    ELSIF TG_OP = 'UPDATE' THEN
>      IF OLD.end_date IS DISTINCT FROM NEW.end_date THEN
>          NEW.sendto := 0;
>          NEW.uts := date_part('epoch', CURRENT_TIMESTAMP)::integer;
>      END IF;
>    END IF;
>    -- lastseen must always be updated with CURRENT_TIMESTAMP if contract 
> is seen
>    NEW.lastseen := CURRENT_TIMESTAMP;
>    RETURN NEW;
> END
> $function$;
> 
> 
> DROP TRIGGER IF EXISTS last_changes ON arhive_table;
> CREATE TRIGGER last_changes
>    BEFORE INSERT OR UPDATE OF end_date ON arhive_table
>    FOR EACH ROW
>    WHEN (pg_trigger_depth() < 1)
>    EXECUTE FUNCTION log_last_chaged();
> 
> 
> Regards,
> HS
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com