Trigger

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

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

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 trigger fills 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