0

I have written program something like below as per my question:

CREATE OR REPLACE FUNCTION hist_base_host_table_trig_func() RETURNS TRIGGER AS $hist_base_host_table_trig$
declare

username VARCHAR(40);


BEGIN
    --
    -- Create a row in emp_audit to reflect the operation performed on emp,
    -- make use of the special variable TG_OP to work out the operation.
    --
       IF (TG_OP = 'DELETE') THEN
    select userid into username 
    from gui_audit_trail 
    WHERE messageid='LS_REF_DATA_ROW_DELETED' 
        and description like '%BASE_HOST%' 
        and to_char(recorded,'YYYY-MM-DD HH:MI:SS')=to_char(OLD.last_update,'YYYY-MM-DD HH:MI:SS');
        RAISE NOTICE 'username is (%)',username;            
        INSERT INTO HIST_BASE_HOST SELECT OLD.zone_id, OLD.site_id, OLD.org_id, OLD.start_date, OLD.end_date, OLD.reserved_capacity, now(),'D', username;
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO HIST_BASE_HOST 
           SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'U',user;
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO HIST_BASE_HOST SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'I',user;
        RETURN NEW;
    END IF;
    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$hist_base_host_table_trig$ LANGUAGE plpgsql;

CREATE TRIGGER hist_base_host_table_trig
AFTER INSERT OR UPDATE OR DELETE ON base_host
    FOR EACH ROW EXECUTE PROCEDURE hist_base_host_table_trig_func();

But "username" is not populating in output, expectation is "administrator" as output.

The two table data is as below:

select * from gui_audit_trail;
 id  |    userid     | moduleid  |    classid    |     typeid     |        messageid        |        recorded         
 718 | administrator | eventlink | CLASS IGNORED | METHOD IGNORED | LS_REF_DATA_ROW_DELETED | 2021-03-10 18:48:02.439

select * from hist_base_host;
 zone_id | site_id |   org_id   | start_date |  end_date  | reserved_capacity |        last_update         | chg_code |   user_id
       5 |       9 | PBTP       | 2002-01-01 | 2015-01-01 |              0.00 | 2021-03-10 18:48:02.413111 | D        |

in select query in above function, I want to cut time till "2021-03-10 18:48:02" from both columns recorded and last_update, but seems its not working, Is there any way to achieve the same functionality?

1 Answer 1

0

If you only want to remove milisecinds

CREATE OR REPLACE FUNCTION hist_base_host_table_trig_func() RETURNS TRIGGER AS $hist_base_host_table_trig$
declare

username VARCHAR(40);


BEGIN
    --
    -- Create a row in emp_audit to reflect the operation performed on emp,
    -- make use of the special variable TG_OP to work out the operation.
    --
       IF (TG_OP = 'DELETE') THEN
    select userid into username 
    from gui_audit_trail 
    WHERE messageid='LS_REF_DATA_ROW_DELETED' 
        and description like '%BASE_HOST%' 
        and date_trunc('second', recorded::timestamp)
        =date_trunc('second', OLD.last_update::timestamp);
        RAISE NOTICE 'username is (%)',username;            
        INSERT INTO HIST_BASE_HOST SELECT OLD.zone_id, OLD.site_id, OLD.org_id, OLD.start_date, OLD.end_date, OLD.reserved_capacity, now(),'D', username;
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO HIST_BASE_HOST 
           SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'U',user;
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO HIST_BASE_HOST SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'I',user;
        RETURN NEW;
    END IF;
    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$hist_base_host_table_trig$ LANGUAGE plpgsql;

CREATE TRIGGER hist_base_host_table_trig
AFTER INSERT OR UPDATE OR DELETE ON base_host
    FOR EACH ROW EXECUTE PROCEDURE hist_base_host_table_trig_func();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.