CODEWITHSUNDEEP
postgresqlddldatabase-trigger
Arthur
Arthur

Reputation: 1176

How to check when postgres trigger was created?

How to check when postgres trigger was modified?

Which column from pg_trigger or how?

Upvotes: 1

Views: 524

Answers (2)

Jaisus
Jaisus

Reputation: 1109

If I understand well your problem is that you want to know when the code of your trigger is updated, and capture this information. (for example in a table). For the previous modifications, it seems impossible on postgresql to know it. But if you are interested in the future modifications then you can put another type of trigger in place : an event trigger. (https://www.postgresql.org/docs/current/event-triggers.html)

And you can do it like this :

CREATE OR REPLACE FUNCTION [your_schema].ddl_trigger_alter_trigger_fct()
  RETURNS event_trigger
 LANGUAGE plpgsql
  AS $$
DECLARE
    all_variables character varying;
    r RECORD;
    schema_name varchar (250);
    command_tag varchar (100);
    object_type varchar (250);
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        if r.schema_name = '[your_schema]' and r.command_tag = 'ALTER TRIGGER'
        then
            schema_name := r.schema_name;
            command_tag := r.command_tag;
            object_type := r.object_type;
            RAISE NOTICE 'caught % event on %', r.command_tag, r.object_identity;
            all_variables := concat(
                'classid: ', r.classid, chr(10)
                , 'objid: ', r.objid, chr(10)
                , 'objsubid: ', r.objsubid, chr(10)
                , 'command_tag: ', r.command_tag, chr(10)
                , 'object_type: ', r.object_type, chr(10)
                , 'schema_name: ', r.schema_name, chr(10)
                , 'object_identity: ', r.object_identity, chr(10)
                , 'in_extension: ', r.in_extension);

            RAISE NOTICE '%', all_variables;
        end if;
    END LOOP;

    if schema_name = '[your_schema]' and command_tag = 'ALTER TRIGGER'
    then
        -- Do what you want, for example insert a line in a table of logs
    end if;
END;
$$;

CREATE EVENT TRIGGER ddl_trigger_alter_trigger ON ddl_command_end WHEN TAG IN ('ALTER TRIGGER')
   EXECUTE FUNCTION [your_schema].ddl_trigger_alter_trigger_fct();

But if it is the code of the function that is called by the trigger that interest you, then you just need to put another event trigger on the ALTER FUNCTION event.

Hope this helps ;)

Upvotes: 1

Sumit Srivastava
Sumit Srivastava

Reputation: 73

Well postgress trigger does not modify, it modifies the attribute value on which you have applied trigger function where you're performing specific operations like, BEFROE UPDATE, BEFORE INSERT, AFTER UPDATE, AFTER INSERT

Upvotes: 1

Related Questions