PostgreSQL Trigger Example

 Triggers can be handy at times, but I tend to stay away from them as long as possible, because they can cause performance degradation at times, plus I tend to forget they're hiding behind the scenes, doing random things.  In any case, let's see how you can create a simple trigger in Postgres.

Steps

1. Create a lookup table
2. Create a parent table
3. Create a child table
4. Create trigger function to convert lookup value to handle Inserts/Updates/Deletes
5. For inserts and updates, convert lookup value to look up id
6. Create trigger
7. Test .. by running several 1000 inserts, updates and deletes

Replace myschema with the name of your schema

-- Create Lookup Table

create table myschema.lookuptable

(

lookupid integer not null,

lookupvalue varchar(50) not null,

constraint pk_lookuptable primary key (lookupid)

);

insert into myschema.lookuptable

(lookupid, lookupvalue)

values (1, 'hello'),

(2, 'bonjour'),

(3, 'hola'),

(4, 'namaste');

-- Create Parent Table

create table myschema.parenttable

(

idcol integer not null primary key,

namecol varchar(50) not null,

datecol timestamp(3),

lookupvalue varchar(50)

);

-- Create Child Table

create table myschema.childtable

(

idcol integer not null primary key,

namecol varchar(50) not null,

datecol timestamp(3),

lookupid integer

);

-- Create Trigger Function

create or replace function trigfunc_parenttable()

returns trigger as $$

declare

int_lookupid integer;

begin

if (TG_OP = 'INSERT') then

select     lookupid 

        into       int_lookupid 

        from       myschema.lookuptable 

        where      lookupvalue = new.lookupvalue;

insert into myschema.childtable

(idcol, namecol, datecol, lookupid)

values (NEW.idcol, NEW.namecol, NEW.datecol, int_lookupid);

elsif (TG_OP = 'UPDATE') then

        select     lookupid 

        into       int_lookupid 

        from       myschema.lookuptable 

        where      lookupvalue = new.lookupvalue;

update myschema.childtable

set namecol = NEW.namecol,

datecol = NEW.datecol,

lookupid = int_lookupid

where idcol = OLD.idcol;

elsif (TG_OP = 'DELETE') then

delete

from myschema.childtable

where idcol = OLD.idcol;

end if;

return NULL;

end $$ language plpgsql;

-- Create Trigger

create trigger trig_parenttable

after insert or update or delete on myschema.parenttable

for each row execute function trigfunc_parenttable();

-- Insert a few 1000 rows

do

$$

declare

int_i integer = 1;

begin

while int_i < 10000 loop

insert into myschema.parenttable

(idcol, namecol, datecol, lookupvalue)

values (int_i, 'hello' || int_i::varchar(5), 

                    current_timestamp, 'hello');

insert into myschema.parenttable

(idcol, namecol, datecol, lookupvalue)

values (int_i + 1, 'bonjour' || 

                    (int_i + 1)::varchar(5), 

                    current_timestamp, 'bonjour');

int_i := int_i + 2;

end loop;

end $$;

-- Run some checks

select     * 

from      myschema.parenttable 

order by idcol;

select     count(*), min(lookupid), max(lookupid) 

from      myschema.childtable 

where namecol like 'hello%';

select count(*), min(lookupid), max(lookupid) 

from  myschema.childtable 

where namecol like 'bonjour%';

You will see that all parent rows were inserted and that all child rows were inserted, 

with LookupIds instead of Lookup values!

-- Update a few 100 rows

update  myschema.parenttable

set namecol = 'hola' || idcol::varchar(5)

where idcol between 1 and 500;

select      * 

from  myschema.parenttable 

where idcol between 1 and 550 

order by idcol;

select * 

from  myschema.childtable 

where idcol between 1 and 550  

order by idcol;

Update worked on both tables!

-- Update a few more rows

update  myschema.parenttable

set lookupvalue = 'hola'

where idcol between 1 and 500;

select * 

from  myschema.parenttable 

where idcol between 1 and 550 

order by idcol;

select * 

from  myschema.childtable 

where idcol between 1 and 550  

order by idcol;

Update worked on both tables and child table rows now have the lookupId for 'Hola'!

-- Delete a few rows

delete

from  myschema.parenttable

where idcol between 501 and 601

select * 

from  myschema.parenttable 

where                 idcol between 491 and 651 

order by idcol;

select * 

from  myschema.childtable 

where idcol between 491 and 651  

order by idcol;

Rows with IDCol between 501 and 601 are gone from both tables!