O'Reilly logo

PostgreSQL Server Programming by Jim Mlodgenski, Kirk Roybal, Hannu Krosing

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Simple "Hey, I'm called" trigger

The first trigger we work on simply sends back a notice to the database client each time the trigger is fired and provides some feedback on its firing conditions:

CREATE OR REPLACE FUNCTION notify_trigger()
  RETURNS TRIGGER AS $$ 
BEGIN 
    RAISE NOTICE 'Hi, I got % invoked FOR % % % on %', 
                               TG_NAME, 
                               TG_LEVEL, 
                               TG_WHEN, 
                               TG_OP, 
                               TG_TABLE_NAME; 
END; 
$$ LANGUAGE plpgsql;

Next, we need a table to bind this function to the following:

CREATE TABLE notify_test(i int);

And we are ready to define the trigger. As we try to be simple here, we define a trigger which is invoked on INSERT and which calls the function once on each row:

CREATE TRIGGER notify_insert_trigger AFTER INSERT ON notify_test FOR EACH ROW EXECUTE PROCEDURE notify_trigger();Let's ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required