Trigger Functions

This is a very basic function - looking for feedback.

-------------------------------------------

Notes:

TRIGGER FUNCTION TO UPDATE WHO CHANGED A RECORD AND WHEN

Basic trigger function in POSTGRE 9.1 created using PGAdmin III.

This results in a server side update to show when and who changed a record.

This is my first one so I am looking for comments and suggestions.

For the record I realize that a more detail audit trail is possible.

The trigger function updates the ChangedAt and ChangedBy fields.

This is defined once and then applied to any table containing these fields using a trigger.

The core data tables all have these four fields which are defined to populate the user and the date on insert.

"CreatedAt" timestamp with time zone DEFAULT now(),

"CreatedBy" character varying(20) DEFAULT "current_user"(),

"ChangedAt" timestamp with time zone DEFAULT now(),

"ChangedBy" character varying(20) DEFAULT "current_user"(),

--------------------------------------------------------------

1. Create the trigger function.

Here is where you right click to create it: "DatabaseName.Schema.YourSchemaName.TriggerFunction.

OVERVIEW OF THE SETTINGS FOR TRIGGER FUNCTION

Name stampchange

OID 17543

Owner Julie

Argument count 0

Arguments

Signature arguments

Return type trigger

Language plpgsql

Returns a set? No

Source BEGIN...

Estimated cost 1

Volatility STABLE

Security of definer? No

Strict? No

Window? No

ACL {=X/Julie,Julie=X/Julie}

System function? No

Comment

FUNCTION

-- Function: stampchange()

-- DROP FUNCTION stampchange();

CREATE OR REPLACE FUNCTION stampchange()

RETURNS trigger AS

$BODY$BEGIN

new."ChangedAt" := now();

new."ChangedBy" := current_user;

return new;

END;$BODY$

LANGUAGE plpgsql STABLE

COST 1;

ALTER FUNCTION stampchange()

OWNER TO "Julie";

GRANT EXECUTE ON FUNCTION stampchange() TO public;

GRANT EXECUTE ON FUNCTION stampchange() TO "Julie";

2. Create the trigger under "DatabaseName.Schema.YourSchemaName.Table.Trigger".

-- Trigger: trig_changeinfo on "tblAcctSub"

-- DROP TRIGGER trig_changeinfo ON "tblAcctSub";

CREATE TRIGGER trig_changeinfo

BEFORE UPDATE

ON "tblAcctSub"

FOR EACH ROW

EXECUTE PROCEDURE stampchange();

3. Right click on the table and select Enable Triggers.

------------------------

Question 1:

Did I miss / overlook the use of a switch/setting?

This will be used across many tables (all relatively 'static' customer inventory records).

I did change from Volatile to Stable.

Question 2:

Is there an optimal place to put standard functions that many databases will call?

The system moved this from 'user' Functions in the database to Trigger Functions.

(It did this right after it worked - and I thought it had disappeared!)

------------------------------

Making progress and thanks for all your help. Lloyd - the cheese was great! Thank you very much.

Best Regards,

Julie Bernhardt
Telecom Advocates
Managing Partner
425 691 0059 (m) 425 502 8424 (o)