Postgres Pro
Downloads
Home   >   mailing lists

Thread: Trigger

Trigger

From
"Andy Samuel"
Date:
How do I create a trigger on a table if a certain row is updated ?
For example, a table called DefaultValue with :
ModuleId char(10),
FieldId char(10),
DefVal char(3)

If I update the table 'update DefaultValue set defval='OFF'  where
FieldId='MODULESTATUS' then it will fire the event.

Thank you in advance
Andy

Re: Trigger

From
Feite Brekeveld
Date:
Andy Samuel wrote:

> How do I create a trigger on a table if a certain row is updated ?
> For example, a table called DefaultValue with :
> ModuleId char(10),
> FieldId char(10),
> DefVal char(3)
>
> If I update the table 'update DefaultValue set defval='OFF'  where
> FieldId='MODULESTATUS' then it will fire the event.

Here is an example of one I use

CREATE FUNCTION "update_seqno" ( ) RETURNS opaque AS '
BEGIN
   new.seqno = nextval(''cdrseqno'');
   new.status = ''U'';
   return new;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER "updseq_no" BEFORE INSERT ON "accounting"  FOR EACH ROW
EXECUTE PROCEDURE "update_seqno" ();

seqno and status are attributes of the accounting-table that do not get set
by the insert statement I use, so the trigger provides the sequence number
from the defined sequence 'cdrseqno' and sets a default status on it.

You can define triggers on [ BEFORE/AFTER ] [ INSERT/DELETE/UPDATE ] .
This all is documented in the docs that come with PostgreSQL. Pay attention
to the double ' when you create functions/triggers.

Regards,

Feite

>
>
> Thank you in advance
> Andy
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
Feite Brekeveld
feite.brekeveld@osiris-it.nl
http://www.osiris-it.nl

RE: Trigger

From
Jeff Eckermann
Date:
Use conditional logic in your function:

CREATE FUNCTION set_defval ()
RETURNS opaque AS '
BEGIN
IF NEW.fieldid = ''MODULESTATUS''
THEN NEW.defval := ''OFF'';
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER default_value_set_defval
BEFORE UPDATE ON default_value
FOR EACH ROW EXECUTE PROCEDURE set_defval();

The trigger will fire for all updates, but will be a no-op for all cases
which don't meet your criterion.
This will create a small performance penalty, but probably not enough to be
noticeable.
I don't think there is a way to have a trigger fire selectively on a given
event.
HTH



> -----Original Message-----
> From:    Andy Samuel [SMTP:andysamuel@geocities.com]
> Sent:    Friday, June 29, 2001 12:39 AM
> To:    pgsql-general@postgresql.org
> Subject:    [GENERAL] Trigger
>
> How do I create a trigger on a table if a certain row is updated ?
> For example, a table called DefaultValue with :
> ModuleId char(10),
> FieldId char(10),
> DefVal char(3)
>
> If I update the table 'update DefaultValue set defval='OFF'  where
> FieldId='MODULESTATUS' then it will fire the event.
>
> Thank you in advance
> Andy
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Re: Trigger

From
"Thalis A. Kalfigopoulos"
Date:
On Fri, 29 Jun 2001, Feite Brekeveld wrote:

> Andy Samuel wrote:
>
> > How do I create a trigger on a table if a certain row is updated ?
> > For example, a table called DefaultValue with :
> > ModuleId char(10),
> > FieldId char(10),
> > DefVal char(3)
> >
> > If I update the table 'update DefaultValue set defval='OFF'  where
> > FieldId='MODULESTATUS' then it will fire the event.
>
> Here is an example of one I use
>
> CREATE FUNCTION "update_seqno" ( ) RETURNS opaque AS '
> BEGIN
>    new.seqno = nextval(''cdrseqno'');
>    new.status = ''U'';
>    return new;
> END;' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER "updseq_no" BEFORE INSERT ON "accounting"  FOR EACH ROW
> EXECUTE PROCEDURE "update_seqno" ();

You don't have to use a trigger in your case. You could instead just set the default values for the fields that the
triggerfills in. Since you didn't do it upon table creation, use ALTER TABLE to do it now and save on the trigger time. 

cheers,
thalis

>
> seqno and status are attributes of the accounting-table that do not get set
> by the insert statement I use, so the trigger provides the sequence number
> from the defined sequence 'cdrseqno' and sets a default status on it.
>
> You can define triggers on [ BEFORE/AFTER ] [ INSERT/DELETE/UPDATE ] .
> This all is documented in the docs that come with PostgreSQL. Pay attention
> to the double ' when you create functions/triggers.
>
> Regards,
>
> Feite
>
> >
> >
> > Thank you in advance
> > Andy
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
> --
> Feite Brekeveld
> feite.brekeveld@osiris-it.nl
> http://www.osiris-it.nl
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>