The first trigger we will work on simply sends a notice back 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 line of code:

CREATE TABLE notify_test(i int);

Now we are ready to define the trigger. As this is a simple example, we define a trigger which is invoked on INSERT and 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 test this out:

postgres=# INSERT INTO notify_test VALUES(1),(2); 

NOTICE:  Hi, I got notify_insert_trigger invoked FOR ROW AFTER INSERT on notify_test 
ERROR:  control reached end of trigger procedure without RETURN 
CONTEXT:  PL/pgSQL function notify_trigger()

Hmm, it seems we need to return something from the function even though it is not needed for our purposes. The function definition says CREATE FUNCTION … RETURNS but we definitely cannot return a trigger from a function.

Let's get back to the documentation. OK, here it is. The trigger needs to return a value of the ROW or RECORD type and it is ignored in the AFTER triggers.

For now, let's just return NEW as this is the right type and is always present even though it will be NULL in the DELETE trigger:

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;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

We can use RETURN NULL as well here, as the return value of the AFTER trigger is ignored anyway:

postgres=# INSERT INTO notify_test VALUES(1),(2); 
NOTICE:  Hi, I got notify_insert_trigger invoked FOR ROW AFTER INSERT on notify_test 
NOTICE:  Hi, I got notify_insert_trigger invoked FOR ROW AFTER INSERT on notify_test 
INSERT 0 2 

As you saw, the trigger function is indeed called once for each row that is inserted, so let's use the same function to also report the UPDATE and DELETE functions:

CREATE TRIGGER notify_update_trigger 
  AFTER UPDATE ON notify_test 
  FOR EACH ROW 
EXECUTE PROCEDURE notify_trigger(); 

CREATE TRIGGER notify_delete_trigger 
  AFTER DELETE ON notify_test 
  FOR EACH ROW 
EXECUTE PROCEDURE notify_trigger(); 

Check whether the preceding code works.

First, let's test the UPDATE trigger:

postgres=# update notify_test set i = i * 10; 
NOTICE:  Hi, I got notify_update_trigger invoked FOR ROW AFTER UPDATE on notify_test 
NOTICE:  Hi, I got notify_update_trigger invoked FOR ROW AFTER UPDATE on notify_test 
UPDATE 2

This works fine—we get a notice for two invocations of our trigger function.

Now, let's test the DELETE trigger:

postgres=# delete from notify_test; 
NOTICE:  Hi, I got notify_delete_trigger invoked FOR ROW AFTER DELETE on notify_test 
NOTICE:  Hi, I got notify_delete_trigger invoked FOR ROW AFTER DELETE on notify_test 
DELETE 2

If you only want to be notified each time an operation is performed on the table, the preceding code is enough. One small improvement can be made in how we define triggers. Instead of creating one trigger for each of the INSERT, UPDATE, or DELETE functions, we can create a single trigger to be called for any of them. So, let's replace the previous three triggers with just the following:

 CREATE TRIGGER notify_trigger 
  AFTER INSERT OR UPDATE OR DELETE 
  ON notify_test 
  FOR EACH ROW 
EXECUTE PROCEDURE notify_trigger();

The ability to put more than one of the INSERT, UPDATE, or DELETE functions in the same trigger definition is a PostgreSQL extension to the SQL standard. Since the action part of the trigger definition is nonstandard anyway, especially when using a PL/pgSQL trigger function, this should not be a problem.

Let's now drop the individual triggers, truncate the table, and test again:

postgres=# DROP TRIGGER notify_insert_trigger ON notify_test; 
DROP TRIGGER 
postgres=# DROP TRIGGER notify_update_trigger ON notify_test; 
DROP TRIGGER 
postgres=# DROP TRIGGER notify_delete_trigger ON notify_test; 
DROP TRIGGER 
postgres=# TRUNCATE notify_test; 
TRUNCATE TABLE 
postgres=# INSERT INTO notify_test VALUES(1); 
NOTICE:  Hi, I got notify_trigger invoked FOR ROW AFTER INSERT on notify_test 
INSERT 0 1 

This works fine but it reveals one weakness: we did not get any notification on TRUNCATE.

Unfortunately, we cannot simply add OR TRUNCATE in the preceding trigger definition. The TRUNCATE command does not act on single rows, so the FOR EACH ROW triggers make no sense for truncating and are not supported.

You need to have a separate trigger definition for TRUNCATE. Fortunately, we can still use the same function, at least for this simple "Hey, I'm called" trigger:

CREATE TRIGGER notify_truncate_trigger 
  AFTER TRUNCATE ON notify_test 
  FOR EACH STATEMENT 
EXECUTE PROCEDURE notify_trigger();

Now we get a notification on TRUNCATE as well, as shown here:

postgres=# TRUNCATE notify_test; 
NOTICE:  Hi, I got notify_truncate_trigger invoked FOR STATEMENT AFTER TRUNCATE on notify_test 
TRUNCATE TABLE

While it may seem cool to get these messages in each Data Manipulation Language (DML) operation, it has little production value.

So, let's develop this a bit further and log the event in an audit log table instead of sending something back to the user.