I have a postgresql tables. user may update records or delete records manually using interface or using query. I need to track how many records have been updated ,deleted into a log table.I had tried as below update operations count can be tracked successfully. But delete operations count are not logging to log table.
CREATE OR REPLACE FUNCTION edmonton.count_func() RETURNS TRIGGER AS $body$
DECLARE
v_old_data TEXT;
v_new_data TEXT;
log_count INTEGER;
BEGIN
IF (TG_OP = 'UPDATE') THEN
v_old_data := ROW(OLD.*);
v_new_data := ROW(NEW.*);
log_count = count(row(new));
RAISE NOTICE 'update operations';
INSERT INTO edmonton.log ("timestamp",message,type,project_area,count)
VALUES (CURRENT_TIMESTAMP,'records updated in Builder
table','Information','edmonton',log_count);
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
v_old_data := ROW(OLD.*);
log_count = count(row(old));
RAISE NOTICE 'delete operations';
INSERT INTO edmonton.log ("timestamp",message,type,project_area,count)
VALUES (CURRENT_TIMESTAMP,'records deleted in Builder
table','Information','edmonton',log_count);
RETURN OLD;
ELSIF (TG_OP = 'INSERT') THEN
v_new_data := ROW(NEW.*);
log_count = count(row(new));
RAISE NOTICE 'insert operations';
INSERT INTO edmonton.log ("timestamp",message,type,project_area,count)
VALUES (CURRENT_TIMESTAMP,'records inserted in Builder
table','Information','edmonton',log_count);
RETURN NEW;
ELSE
RAISE WARNING '[EDMONTON.COUNT_FUNC] - Other action occurred: %, at
%',TG_OP,now();
RETURN NULL;
END IF;
EXCEPTION
WHEN data_exception THEN
RAISE WARNING '[EDMONTON.COUNT_FUNC] - UDF ERROR [DATA EXCEPTION] -
SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN unique_violation THEN
RAISE WARNING '[EDMONTON.COUNT_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: %,
SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN OTHERS THEN
RAISE WARNING '[EDMONTON.COUNT_FUNC] - UDF ERROR [OTHER] - SQLSTATE: %,
SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
END;
$body$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, edmonton;
And this is my trigger
CREATE TRIGGER count_audit
AFTER INSERT OR UPDATE OR DELETE
ON edmonton.builder
FOR EACH STATEMENT
EXECUTE PROCEDURE edmonton.count_func();
I am getting a warning message when updating a record and messages were not logged to log table.
WARNING: [EDMONTON.COUNT_FUNC] - UDF ERROR [OTHER] - SQLSTATE: 55000, SQLERRM: record "old" is not assigned yet
CONTEXT: PL/pgSQL function count_func() line 49 at RAISE
