From: | "Albin, Lloyd P" <lalbin(at)scharp(dot)org> |
---|---|
To: | "seapug(at)postgresql(dot)org" <seapug(at)postgresql(dot)org> |
Subject: | Re: Trigger Functions |
Date: | 2013-02-14 00:46:37 |
Message-ID: | AE011E7AE62117479360E1E2BD341F4E02BDEB@adama.fhcrc.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | seapug |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Albin, Lloyd P | 2013-02-14 00:53:53 | Re: SEAPUG website |
Previous Message | Albin, Lloyd P | 2013-02-13 23:55:49 | Re: Trigger Functions |