Quick Links

Re: Trigger only firing once

From:	Fran Fabrizio <ffabrizio(at)Exchange(dot)WebMD(dot)net>
To:	pgsql-general(at)postgresql(dot)org
Subject:	Re: Trigger only firing once
Date:	2001-05-14 16:04:25
Message-ID:	3B000208.66B6707@exchange.webmd.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-general

> Kinda hard to believe.

I know it, but that's what I am seeing..... (to recap, my trigger only
fires on the first insert per connection.)

This will be kind of long, it's a spliced-together version of my psql
client session and the server log. I drop and re-create the procedure
and trigger, then I'll connect and test, and disconnect and reconnect and
show you what's happening. Comments with ***** stars around them
**** are my running commentary to make following this session easier.

***** First, I use the text file to drop and recreate the procedure and
trigger. *****

postgres(at)rusty ~$ psql monitoring < log_trigger
DROP
DROP
CREATE
CREATE
postgres(at)rusty ~$

***** Server log shows dropping and creating (long procedure, sorry)
*****

2001-05-14 11:51:12 DEBUG: StartTransactionCommand
2001-05-14 11:51:12 DEBUG: query: /* trigger to update the host table
for incoming records
The trigger will update the update the date and timestamps
for the host, insert a newhost if one is not found */
drop function update_host_table();
2001-05-14 11:51:12 DEBUG: ProcessUtility: /* trigger to update the host
table for incoming records
The trigger will update the update the date and timestamps
for the host, insert a newhost if one is not found */
drop function update_host_table();
2001-05-14 11:51:12 DEBUG: CommitTransactionCommand
2001-05-14 11:51:12 DEBUG: StartTransactionCommand
2001-05-14 11:51:12 DEBUG: query: drop trigger log_trigger on log ;
2001-05-14 11:51:12 DEBUG: ProcessUtility: drop trigger log_trigger on
log ;
2001-05-14 11:51:12 DEBUG: CommitTransactionCommand
2001-05-14 11:51:12 DEBUG: StartTransactionCommand
2001-05-14 11:51:12 DEBUG: query: create function update_host_table()
returns opaque
as 'declare

site_rec record;
host_rec record;
status_rec record;

begin

new.tstamp := now() ;
/* check to see if we have see this site before */

select * into site_rec
from sites s
where s.fqdn = new.fqdn ;

/* -- if we have not found the machine name we are going to
insert a new record into the sites table and set the init_contact to now
*/

if not found
then
insert into sites
values
(nextval(''sites_site_id_seq''),new.fqdn,new.site,new.region,''f'',new.tstamp)
;

/* we also have to insert a new host if this is a new site */

insert into hosts
values
(nextval(''hosts_host_id_seq''),currval(''sites_site_id_seq''),new.hostname,new.tstamp)
;

/* now update the incoming record with the new host_id and
site_id */
new.site_id=currval(''sites_site_id_seq'');
new.host_id=currval(''hosts_host_id_seq'');

else
/* we have seen the site before, update the incoming records
site_id */

new.site_id = site_rec.site_id ;

/* if we have seen this site before we need to check and see if
we have ever seen this machine before */

select * into host_rec
from hosts h
where h.hostname = new.hostname and
h.site_id = site_rec.site_id ;

/* new host */
if not found
then
insert into hosts
values
(nextval(''hosts_host_id_seq''),site_rec.site_id,new.hostname,new.tstamp)
;
new.host_id = currval(''hosts_host_id_seq'');
else
new.host_id = host_rec.host_id ;

update hosts
set last_contact = new.tstamp
where hosts.host_id = new.host_id ;
end if ;

/* update sites
set last_contact = new.tstamp
where sites.fqdn = new.fqdn ; */
end if ;

/* now we are going to update the status table with the new record */

select * into status_rec
from status s where
s.site_id = new.site_id and
s.host_id = new.host_id and
s.product = new.product and
s.class = new.class and
s.subclass = new.subclass ;

/* new monitored process */

if not found
then
insert into status
values (new.site_id,
new.host_id,
new.product,
new.class,
new.subclass,
new.status,
new.msg,
new.tstamp);
else
update status
set status = new.status,
tstamp = new.tstamp
where site_id = new.site_id and
host_id = new.host_id and
product = new.product and
class = new.class and
subclass = new.subclass ;
end if ;
return new;
end ;'
language 'plpgsql';
2001-05-14 11:51:12 DEBUG: ProcessUtility: create function
update_host_table()
returns opaque
as 'declare