Re: Trigger Functions

I forgot this one item when I wrote my response the last time.

You used varchar(20) for the usernames and internally they are type name. So your code could be changed to:

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

"CreatedBy" name DEFAULT "current_user"(),

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

"ChangedBy" name DEFAULT "current_user"(),

The size of name, which is also used for schema, table, view, etc names, is settable via NAMEDATALEN at build time or via max_identifier_length in the postgresql.conf, so it is best to not hard code the length of name. In build 9.0 through 9.2, the length is a max of 63 characters when using multibyte encodings.

Lloyd

From: seapug-owner(at)postgresql(dot)org [mailto:seapug-owner(at)postgresql(dot)org] On Behalf Of Julie Bernhardt
Sent: Wednesday, February 13, 2013 12:48 PM
To: seapug(at)postgresql(dot)org
Subject: [seapug] 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)