Feed on
Posts
Comments
«
»

Multi-table trigger in PostgreSQL rocks!

Feb 26th, 2011 by

>There are a plenty of commercial and open source database technologies, out of which I find PostgreSQL as one of the best database management technology. In this blog, we will discuss about a unique feature PostgreSQL which you will not find even in the commercial database platforms such as Oracle or Microsoft SQL Server.

Can a trigger be used for/in more than one table?
With PostgreSQL, it’s possible. The separation of trigger function and trigger object helps PostgreSQL achieve such functionality.

Why do you need to share a stupid trigger for two more tables?
Sharing a trigger helps reduce errors in database,  and it’s one of most innovative features of PostgreSQL database I have found so far. I will try to explain the usage of a single trigger being accessed by most (if not all) of the tables of a database.

Let’s start with a couple of tables and data

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

CREATE TABLE test
(
    test_id SERIAL NOT NULL PRIMARY KEY,
    item VARCHAR
);
 
INSERT INTO test(item)
SELECT 'A' UNION ALL
SELECT 'quick' UNION ALL
SELECT 'brown' UNION ALL
SELECT 'fox' UNION ALL
SELECT 'jumps' UNION ALL
SELECT 'over' UNION ALL
SELECT 'the' UNION ALL
SELECT 'lazy' UNION ALL
SELECT 'dog';
 
CREATE TABLE practice
(
    practice_id SERIAL NOT NULL PRIMARY KEY,
    lesson VARCHAR NULL
);
 
INSERT INTO practice(lesson)
SELECT 'Windows' UNION ALL
SELECT 'Oracle' UNION ALL
SELECT 'IIS' UNION ALL
SELECT 'C#';

Yet a new table

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE SCHEMA LOG;
 
CREATE TABLE LOG.history
(
    history_id BIGSERIAL NOT NULL PRIMARY KEY,
    action VARCHAR NOT NULL,
    event_date TIMESTAMP NOT NULL DEFAULT(NOW()),
    USER VARCHAR NOT NULL DEFAULT(current_user),
    schema_name VARCHAR NOT NULL,
    table_name VARCHAR NOT NULL,
    pkey_id VARCHAR NOT NULL,
    column_name VARCHAR NOT NULL,
    old_val VARCHAR NULL,
    new_val VARCHAR NULL
);

In most of the database, we would have to keep some audit trails of information change, when did it happen, who did that, and what was changed to what? Here, we will create a single function (trigger function) which will be applied as triggers objects later-on to the tables we’d created previously.

Function log.update_history()

Selec All Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

CREATE OR REPLACE FUNCTION LOG.update_history()
RETURNS TRIGGER AS
$$
    DECLARE each_column RECORD;
    DECLARE each_entity RECORD;
    DECLARE column_name VARCHAR;
    DECLARE old_value VARCHAR;
    DECLARE new_value VARCHAR;
    DECLARE pkey_column_name VARCHAR;
    DECLARE pkey_value VARCHAR;
BEGIN
    SELECT 
        INTO pkey_column_name
        kcu.column_name
    FROM 
        information_schema.table_constraints AS tc 
        JOIN information_schema.key_column_usage AS kcu 
        ON tc.constraint_name = kcu.constraint_name
        JOIN information_schema.constraint_column_usage AS ccu 
        ON ccu.constraint_name = tc.constraint_name
    WHERE 
        constraint_type = 'PRIMARY KEY' 
    AND 
        ccu.table_name=tg_relname;
 
    FOR each_column IN
        SELECT 
            attname AS column_name 
        FROM 
            pg_attribute 
        WHERE 
            attrelid = 
            (
                SELECT 
                    oid 
                FROM 
                    pg_class 
                WHERE 
                    relname = tg_relname
            )
        AND attnum > 0
    LOOP
 
        FOR each_entity IN
            EXECUTE 'SELECT text("' || each_column.column_name || '") as old_val, 
 
                text((' || QUOTE_LITERAL(NEW) || '::"' || tg_table_schema || '"."' || tg_relname || '")."'  || each_column.column_name || '") 
                AS new_val, 
 
                text((' || QUOTE_LITERAL(OLD) || '::"' || tg_table_schema || '"."' || tg_relname || '")."'  || pkey_column_name || '") 
                AS pk_val 
 
                FROM "' || tg_table_schema || '"."' || tg_relname || '" WHERE "' || pkey_column_name ||  '"= 
                (' || QUOTE_LITERAL(OLD) || '::"' || tg_table_schema || '"."' || tg_relname || '")."'  || pkey_column_name || '";'
        LOOP
 
            old_value = each_entity.old_val;
            new_value =  each_entity.new_val;
            pkey_value = each_entity.pk_val;
 
            IF old_value != new_value THEN
 
                INSERT INTO LOG.history
                (
                    action,
                    table_name,
                    schema_name, 
                    pkey_id, 
                    column_name, 
                    old_val, 
                    new_val
                ) 
                VALUES 
                (
                    tg_op, 
                    tg_relname, 
                    tg_table_schema, 
                    pkey_value, 
                    each_column.column_name, 
                    old_value,
                    new_value
                );
            END IF;
        END LOOP;
    END LOOP;
    RETURN NEW;
END
$$
LANGUAGE plpgsql;

Let’s Bind the Trigger to Table(s)

Selec All Code:
1
2
3
4
5
6
7
8
9

CREATE TRIGGER update_history_test_trigger
BEFORE UPDATE
ON test
FOR EACH ROW EXECUTE PROCEDURE LOG.update_history();
 
CREATE TRIGGER update_history_practice_trigger
BEFORE UPDATE
ON practice
FOR EACH ROW EXECUTE PROCEDURE LOG.update_history();

Let’s commit the crime

Selec All Code:
1
2
3
4
5
6
7
8

UPDATE test SET item='white'        WHERE item='brown';
UPDATE test SET item='The'          WHERE item='A';
UPDATE test SET item='energetic'    WHERE item='lazy';
 
UPDATE practice SET lesson='Linux'       WHERE lesson='Windows';
UPDATE practice SET lesson='Postgres'    WHERE lesson='Oracle';
UPDATE practice SET lesson='Apache'      WHERE lesson='IIS';
UPDATE practice SET lesson='Perl'        WHERE lesson='C#';

Let’s test it

Selec All Code:
1

SELECT * FROM LOG.history ORDER BY event_date;

Result

Selec All Code:
1
2
3
4
5
6
7
8

# act  event_date               USER      schema  TABLE     p  col     old      NEW
1 UPD  2011-02-26 09:19:34.504  postgres  PUBLIC  test      3  item    brown    white
2 UPD  2011-02-26 09:19:34.504  postgres  PUBLIC  test      1  item    A        The
3 UPD  2011-02-26 09:19:34.504  postgres  PUBLIC  test      8  item    lazy     energetic
4 UPD  2011-02-26 09:19:34.504  postgres  PUBLIC  practice  1  lesson  Windows  Linux
5 UPD  2011-02-26 09:19:34.504  postgres  PUBLIC  practice  2  lesson  Oracle   Postgres
6 UPD  2011-02-26 09:19:34.504  postgres  PUBLIC  practice  3  lesson  IIS      Apache 
7 UPD  2011-02-26 09:19:34.504  postgres  PUBLIC  practice  4  lesson  C#       Perl

Conclusion

This feature helps DBAdmin remain DRY by maintaining a centralized function.

Do you have more examples?
If you have more example on this topic, please share them as comments. :-)

Tags: , , , ,

Posted in PostgreSQL Server

8 Responses to “Multi-table trigger in PostgreSQL rocks!”

  1. Pavel St?hule says:
    February 26, 2011 at 5:16 am

    >nice academics example – but it can be slow.

    please, read:

    old_value = each_entity.old_val;
    new_value = each_entity.new_val;
    pkey_value = each_entity.pk_val;

    it's useless – plpgsql isn't compiled language – it means three more SELECTs inside code.

    Iteration over record without C modules isn't good technique too – use a hstore or pltoolbox

    http://okbob.blogspot.com/2010/12/iteration-over-record-in-plpgsql.html.

    Performance is reason why people don't prefer a generic triggers.

    Reply
  2. Valentine Gogichashvili says:
    February 26, 2011 at 11:36 am

    >You are forgetting to quote_ident() the schema, table and column names… in the code, you have shown. It will fail on object names with capital letters and spaces…

    Reply
  3. Valentine Gogichashvili says:
    February 26, 2011 at 11:38 am

    >Oh… sorry, without quote_ident it will fail only if the object name contains some specially quoted SQL commands with spaces :) :) did not see the " quotes you are putting there… but still probably quote_ident() should be safer.

    Reply
  4. Binod Nepal says:
    February 26, 2011 at 12:52 pm

    >I have tested the above code to work with Mixed case/lower case and it works. Well, I have not checked the same with spaces.

    Reply
  5. Valentine Gogichashvili says:
    February 28, 2011 at 5:36 pm

    >It will work with the spaces, it will not work with a column like:

    A very "strange" column name

    and with the column name like:

    c1" and bla, "blu"."c2

    could even do some result manipulation :)

    So it is just safer to always use quote_ident() when putting identifiers into the query, that you build.

    http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

    Reply
  6. Binod Nepal says:
    February 28, 2011 at 5:40 pm

    >I will post new version of this function with quote_ident(). Thank you valentine.

    Reply
  7. Daniel Cristian says:
    March 1, 2011 at 1:23 pm

    >Sometimes I use a register table with (id, creation timestamp, creator, update timestamp, updater, etc) and use it with inheritance on tables which need this kind of log.
    So, with just two trigger function (INSERT/UPDATE), I do the same job on n tables.

    Reply
  8. Thebad says:
    June 29, 2011 at 6:04 pm

    >(i am no dba, so forgive me if this question is naive)

    why run the trigger "BEFORE UPDATE" as if it was run "After UPDATE" then it would catch any changes made by other triggers to the table?

    Reply

Leave a Reply