$ createlang plpgsql template1 -Upostgres     # 'installs' the plpgsql language
$ dropdb sql4wn -Upostgres     # start with a fresh database for our experiment
$ createdb sql4wn -Upostgres
CREATE DATABASE

drop table phone_numbers;
drop table mailing_list;

create table mailing_list (
        email           varchar(100) not null primary key,
        name            varchar(100)
);

create table phone_numbers (
        email           varchar(100) not null references mailing_list(email),
        number_type     varchar(15) check (number_type in ('work','home','cell','beeper')),
        phone_number    varchar(20) not null
);

insert into mailing_list (name, email) values ('Philip Greenspun','philg@mit.edu');
insert into mailing_list (name, email) values ('Michael O''Grady','ogrady@fastbuck.com');
insert into phone_numbers values ('ogrady@fastbuck.com','work','(800) 555-1212');
insert into phone_numbers values ('ogrady@fastbuck.com','home','(617) 495-6000');
insert into phone_numbers values ('philg@mit.edu','work','(617) 253-8574');

alter table mailing_list add registration_date date;

-- WE DROP THE TRIGGER AND FUNCTION HERE,
-- EXPECTING TO GET ERRORS ONLY THE FIRST
-- TIME WE RUN THIS SCRIPT
drop trigger mailing_list_registration_date_trigger on mailing_list;
drop function mailing_list_registration_date();

-- HERE'S A FUNCTION THAT WE'LL USE IN THE TRIGGER
create function mailing_list_registration_date() returns trigger as $mailing_list_registration_date$
    begin
        if new.registration_date is null then
            new.registration_date := 'now';
        end if;
        return new;
    end;
$mailing_list_registration_date$ language plpgsql;

-- HERE'S THE TRIGGER
create trigger mailing_list_registration_date_trigger before insert or update on mailing_list
    for each row execute procedure mailing_list_registration_date();

-- Now we can test the trigger
insert into mailing_list (email, name) values ('testing@triggers.org', 'Trigger Tester');
-- We expect the row inserted above to not have a null date field in the output
-- from the below select command thanks to our function/trigger combo
select * from mailing_list;

$ psql sql4wn -Upostgres < TriggerDroppingError.sql
ERROR:  table "phone_numbers" does not exist
ERROR:  table "mailing_list" does not exist
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mailing_list_pkey" for table "mailing_list"
CREATE TABLE
CREATE TABLE
INSERT 18272 1
INSERT 18273 1
INSERT 18274 1
INSERT 18275 1
INSERT 18276 1
ALTER TABLE
ERROR:  trigger "mailing_list_registration_date_trigger" for table "mailing_list" does not exist
ERROR:  function mailing_list_registration_date() does not exist
CREATE FUNCTION
CREATE TRIGGER
INSERT 18279 1
        email         |       name       | registration_date
----------------------+------------------+-------------------
 philg@mit.edu        | Philip Greenspun |
 ogrady@fastbuck.com  | Michael O'Grady  |
 testing@triggers.org | Trigger Tester   | 2005-08-01
(3 rows)

$ psql sql4wn -Upostgres < TriggerDroppingError.sql
DROP TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mailing_list_pkey" for table "mailing_list"
CREATE TABLE
CREATE TABLE
INSERT 18291 1
INSERT 18292 1
INSERT 18293 1
INSERT 18294 1
INSERT 18295 1
ALTER TABLE
ERROR:  trigger "mailing_list_registration_date_trigger" for table "mailing_list" does not exist
DROP FUNCTION
CREATE FUNCTION
CREATE TRIGGER
INSERT 18298 1
        email         |       name       | registration_date
----------------------+------------------+-------------------
 philg@mit.edu        | Philip Greenspun |
 ogrady@fastbuck.com  | Michael O'Grady  |
 testing@triggers.org | Trigger Tester   | 2005-08-01
(3 rows)

$

Welcome to psql 8.0.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

sql4wn=# drop trigger mailing_list_registration_date_trigger on mailing_list;
DROP TRIGGER
sql4wn=#

drop trigger mailing_list_registration_date_trigger on mailing_list;
drop function mailing_list_registration_date();
drop table phone_numbers;
drop table mailing_list;