agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedTrigger
14+ messages / 12 participants
[nested] [flat]
* Trigger
@ 2000-07-14 14:11 Carolyn Lu Wong <carolyn@kss.net.au>
0 siblings, 1 reply; 14+ messages in thread
From: Carolyn Lu Wong @ 2000-07-14 14:11 UTC (permalink / raw)
To: pgsql-sql
I'm using V6.5.3.
I'm trying to create transaction logs for particular table, so I need to
write trigger on insert/delete/update on each row. Are there any
examples/documents that can help me with this?
In the "Interaction with the Trigger Manager" from the online document,
it states that it's for V7.1 or later. Because I'm working on earlier
version, do I need to modify my code if the database is upgraded to
later versions?
I'm writing the C program in under my home directory. How can I set up
the environment so that my program compiles?
Thanks in advance for any helps and directions.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Trigger
@ 2000-07-14 15:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Carolyn Lu Wong <carolyn@kss.net.au>
0 siblings, 0 replies; 14+ messages in thread
From: Tom Lane @ 2000-07-14 15:49 UTC (permalink / raw)
To: Carolyn Lu Wong <carolyn@kss.net.au>; +Cc: pgsql-sql
Carolyn Lu Wong <carolyn@kss.net.au> writes:
> In the "Interaction with the Trigger Manager" from the online document,
> it states that it's for V7.1 or later. Because I'm working on earlier
> version, do I need to modify my code if the database is upgraded to
> later versions?
The trigger interface for 7.0 is the same as in 6.5, so you can rely
on the online document for now.
7.1 will have a different trigger interface (but the document hasn't
been updated yet). The key difference is that the TriggerData structure
will be passed as a parameter rather than being pointed to by a global
variable. You can minimize the number of changes you will need to make
by not referring to CurrentTriggerData all over the place --- instead,
copy it into a local variable at the start of your trigger function.
> I'm writing the C program in under my home directory. How can I set up
> the environment so that my program compiles?
Easiest way is to point -I at the src/include tree, I'm afraid. There
are some examples of C triggers in the contrib/ area, take a look at
their makefiles.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Trigger
@ 2000-09-06 10:24 Mike Baroukh <mbaroukh@i-panema.fr>
parent: Craig May <craig.may@s2.enthdimension.com.au>
1 sibling, 0 replies; 14+ messages in thread
From: Mike Baroukh @ 2000-09-06 10:24 UTC (permalink / raw)
To: craig.may@s2.enthdimension.com.au; pgsql-sql
There is a sample in postgres documentation. (See below).
the only problem is for using langage plpgsql.
If it is not understand by your database, you must use command
createlang plpgsql dbname
as the owner of the database.
CREATE TABLE emp (
empname text,
salary int4,
last_date datetime,
last_user name);
CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS
BEGIN
-- Check that empname and salary are given
IF NEW.empname ISNULL THEN
RAISE EXCEPTION ''empname cannot be NULL value'';
END IF;
IF NEW.salary ISNULL THEN
RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
END IF;
-- Who works for us when she must pay for?
IF NEW.salary < 0 THEN
RAISE EXCEPTION ''% cannot have a negative salary'',
NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := ''now'';
NEW.last_user := getpgusername();
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
----- Original Message -----
From: Craig May <craig.may@s2.enthdimension.com.au>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, September 06, 2000 10:27 PM
Subject: [SQL] Trigger
>
> Could someone send me a quick example of a trigger.
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Trigger
@ 2000-09-06 12:18 Chris Ryan <chris@greatbridge.com>
parent: Craig May <craig.may@s2.enthdimension.com.au>
1 sibling, 1 reply; 14+ messages in thread
From: Chris Ryan @ 2000-09-06 12:18 UTC (permalink / raw)
To: craig.may@s2.enthdimension.com.au; +Cc: pgsql-sql
Craig May wrote:
>
> Could someone send me a quick example of a trigger.
Hope this helps.
Chris Ryan
<<< Clip below and execute to create example >>>
--
-- FUNCTION: trigger_last_updated
--
-- DESCRIPTION:
-- This is a function called by the table triggers to update the
last_updated
-- field on insert and updates.
--
create function trigger_last_updated()
returns opaque
as 'begin
new.last_updated := now();
return new;
end;'
language 'plpgsql';
--
-- TABLE: test_tbl
--
-- DESCRIPTION:
-- A simple table to test my trigger
--
create table test_tbl (
some_field varchar(10),
last_updated timestamp not null default now()
);
--
-- TRIGGER: trigger_insert_update_test_tbl
--
-- DESCRIPTION:
-- This is the trigger called on insert and updates of all the table
that
-- has the last_updated field. It will use the function
trigger_last_updated
-- The cool thing here is the function doesn't make specific reference
to the
-- table so you could create a different trigger for each table with the
field
-- last_updated and use the same function.
--
create trigger trigger_insert_update_test_tbl
before insert or update on test_tbl
for each row execute procedure trigger_last_updated();
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Trigger
@ 2000-09-06 12:40 Chris Ryan <chris@greatbridge.com>
parent: Chris Ryan <chris@greatbridge.com>
0 siblings, 0 replies; 14+ messages in thread
From: Chris Ryan @ 2000-09-06 12:40 UTC (permalink / raw)
To: craig.may@s2.enthdimension.com.au; pgsql-sql
Chris Ryan wrote:
>
> Craig May wrote:
> >
> > Could someone send me a quick example of a trigger.
>
> Hope this helps.
>
> Chris Ryan
>
-- snipped code --
I am so sorry but you may have noticed my email client wrapped lines it
shouldn't have. I have attached the file this time.
Chris Ryan
--
-- FILE: trigger_example.sql
--
-- DESCRIPTION:
-- This file shows the basics of creating a table with a trigger
--
-- Chris Ryan <chris@greatbridge.com> 09/06/2000
--
-- GENERAL DISCLAIMER:
-- Please feel free to use this in any way you see fit to copy, modify,
-- redistribute, etc.. I provide not warranty of the code nor may I be held
-- responsible for it's use/misuse should something bad happen including
-- intentional or acts of god.
--
--
-- FUNCTION: trigger_last_updated
--
-- DESCRIPTION:
-- This is a function called by the table triggers to update the last_updated
-- field on insert and updates.
--
create function trigger_last_updated()
returns opaque
as 'begin
new.last_updated := now();
return new;
end;'
language 'plpgsql';
--
-- TABLE: test_tbl
--
-- DESCRIPTION:
-- A simple table to test my trigger
--
create table test_tbl (
some_field varchar(10),
last_updated timestamp not null default now()
);
--
-- TRIGGER: trigger_insert_update_test_tbl
--
-- DESCRIPTION:
-- This is the trigger called on insert and updates of all the table that
-- has the last_updated field. It will use the function trigger_last_updated
-- The cool thing here is the function doesn't make specific reference to the
-- table so you could create a different trigger for each table with the field
-- last_updated and use the same function.
--
create trigger trigger_insert_update_test_tbl
before insert or update on test_tbl
for each row execute procedure trigger_last_updated();
Attachments:
[text/plain] trigger_example.sql (1.5K, ../../39B63B34.116C525F@greatbridge.com/2-trigger_example.sql)
download | inline:
--
-- FILE: trigger_example.sql
--
-- DESCRIPTION:
-- This file shows the basics of creating a table with a trigger
--
-- Chris Ryan <chris@greatbridge.com> 09/06/2000
--
-- GENERAL DISCLAIMER:
-- Please feel free to use this in any way you see fit to copy, modify,
-- redistribute, etc.. I provide not warranty of the code nor may I be held
-- responsible for it's use/misuse should something bad happen including
-- intentional or acts of god.
--
--
-- FUNCTION: trigger_last_updated
--
-- DESCRIPTION:
-- This is a function called by the table triggers to update the last_updated
-- field on insert and updates.
--
create function trigger_last_updated()
returns opaque
as 'begin
new.last_updated := now();
return new;
end;'
language 'plpgsql';
--
-- TABLE: test_tbl
--
-- DESCRIPTION:
-- A simple table to test my trigger
--
create table test_tbl (
some_field varchar(10),
last_updated timestamp not null default now()
);
--
-- TRIGGER: trigger_insert_update_test_tbl
--
-- DESCRIPTION:
-- This is the trigger called on insert and updates of all the table that
-- has the last_updated field. It will use the function trigger_last_updated
-- The cool thing here is the function doesn't make specific reference to the
-- table so you could create a different trigger for each table with the field
-- last_updated and use the same function.
--
create trigger trigger_insert_update_test_tbl
before insert or update on test_tbl
for each row execute procedure trigger_last_updated();
^ permalink raw reply [nested|flat] 14+ messages in thread
* Trigger
@ 2000-09-06 20:27 Craig May <craig.may@s2.enthdimension.com.au>
0 siblings, 2 replies; 14+ messages in thread
From: Craig May @ 2000-09-06 20:27 UTC (permalink / raw)
To: pgsql-sql
Could someone send me a quick example of a trigger.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Trigger
@ 2003-04-10 16:07 A.Bhuvaneswaran <bhuvansql@myrealbox.com>
parent: Stefan Sturm <mailling@anrath.info>
2 siblings, 0 replies; 14+ messages in thread
From: A.Bhuvaneswaran @ 2003-04-10 16:07 UTC (permalink / raw)
To: Stefan Sturm <mailling@anrath.info>; +Cc: pgsql-sql
> I switch to PostgreSQL 7.3 and now I try to make some Triggers. But the
> triggers are differnt to Oracle. Where can I find a tutorial about using
> and creating triggers in PostgreSQL 7.3
Follow the below link to learn howto write triggered procedures:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=plpgsql-trigger.html
regards,
bhuvaneswaran
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Trigger
@ 2003-04-10 16:12 Franco Bruno Borghesi <franco@akyasociados.com.ar>
parent: Stefan Sturm <mailling@anrath.info>
2 siblings, 0 replies; 14+ messages in thread
From: Franco Bruno Borghesi @ 2003-04-10 16:12 UTC (permalink / raw)
To: Stefan Sturm <mailling@anrath.info>; pgsql-sql
I usually use plpgsql to program my triggers. The plpgsql manual is here:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=plpgsql.html
Section 19.9 specifically talks about triggers.
Anyway, here is an example of what you said you needed:
--my table
CREATE TABLE mytest (id SERIAL, name TEXT, lastchange TIMESTAMP);
--function to set the value of the field 'lastChange' to current system tyme
CREATE OR REPLACE FUNCTION mytest_set_lastChange() RETURNS TRIGGER AS '
BEGIN
NEW.lastChange:=current_timestamp;
RETURN NEW;
END; ' LANGUAGE 'plpgsql';
--trigger that calls mytest_set_lastChange after inserts or updates
CREATE TRIGGER mytest_tg1 BEFORE INSERT OR UPDATE ON mytest FOR EACH ROW
EXECUTE PROCEDURE mytest_set_lastChange();
INSERT INTO mytest (name) VALUES ('peter');
SELECT * FROM mytest;
id | name | lastchange
----+-------+----------------------------
1 | peter | 2003-04-10 13:10:16.993779
(1 row)
UPDATE mytest SET name='joe';
id | name | lastchange
----+------+----------------------------
1 | joe | 2003-04-10 13:11:10.787253
(1 row)
hope it helps.
On Wednesday 10 April 2024 11:58, you wrote:
> t want to update to fields on instert and update with the system
> time...
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Trigger
@ 2003-04-10 17:54 Richard Huxton <dev@archonet.com>
parent: Stefan Sturm <mailling@anrath.info>
2 siblings, 0 replies; 14+ messages in thread
From: Richard Huxton @ 2003-04-10 17:54 UTC (permalink / raw)
To: Stefan Sturm <mailling@anrath.info>; pgsql-sql
On Wednesday 10 Apr 2024 3:58 pm, Stefan Sturm wrote:
> Hello,
>
> I switch to PostgreSQL 7.3 and now I try to make some Triggers. But the
> triggers are differnt to Oracle. Where can I find a tutorial about using
> and creating triggers in PostgreSQL 7.3
>
> I just want to update to fields on instert and update with the system
> time...
You've already got some answers pointing you to the relevant point in the
documentation, but you might like to check out
http://techdocs.postgresql.org/ - lots of good stuff there.
Especially, it might be worth checking the cookbook (Roberto Mello) and the
Postgresql Notes (me). I think I've got an example of this in the notes,
although it's probably out of date.
--
Richard Huxton
^ permalink raw reply [nested|flat] 14+ messages in thread
* Trigger
@ 2005-02-16 13:44 Eugen Gass <gass@intend.de>
0 siblings, 2 replies; 14+ messages in thread
From: Eugen Gass @ 2005-02-16 13:44 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm trying to create a trigger on PostgreSQL
it should be like an oracle(sql) sample code:
create or replace trigger frei_polygon_sync
after INSERT on frei_polygon
Referencing NEW as newROW
for each row
Begin
:newRow.objektid := :newRow.gid;
:
end;
Can sombody help me to do the same on Postrgres
Thanks
Best Regards
EG
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Trigger
@ 2005-02-16 20:53 Pavel Rabel <pavel@sajt.cz>
parent: Eugen Gass <gass@intend.de>
1 sibling, 0 replies; 14+ messages in thread
From: Pavel Rabel @ 2005-02-16 20:53 UTC (permalink / raw)
To: pgsql-sql
It requires a bit more work in PostgreSQL to create a trigger.
From the documentation: "It is not currently possible to write a
SQL-language trigger function. Trigger functions can be written in C or
in some of the available procedural languages."
I guess you will prefer to write the trigger in PL/pgSQL, have a look at
http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html
Regards
Pavel
Eugen Gass wrote:
>Hi,
>
>I'm trying to create a trigger on PostgreSQL
>
>it should be like an oracle(sql) sample code:
>
>create or replace trigger frei_polygon_sync
>after INSERT on frei_polygon
>Referencing NEW as newROW
>for each row
>Begin
> :newRow.objektid := :newRow.gid;
> :
>end;
>
>Can sombody help me to do the same on Postrgres
>
>Thanks
>
>Best Regards
>
>EG
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Trigger
@ 2005-02-16 23:47 Michael Fuhr <mike@fuhr.org>
parent: Eugen Gass <gass@intend.de>
1 sibling, 0 replies; 14+ messages in thread
From: Michael Fuhr @ 2005-02-16 23:47 UTC (permalink / raw)
To: Eugen Gass <gass@intend.de>; +Cc: pgsql-sql
On Wed, Feb 16, 2005 at 02:44:30PM +0100, Eugen Gass wrote:
>
> I'm trying to create a trigger on PostgreSQL
See the "Server Programming" part of the documentation (substitute
the appropriate version of PostgreSQL in the links):
http://www.postgresql.org/docs/8.0/static/server-programming.html
http://www.postgresql.org/docs/8.0/static/triggers.html
http://www.postgresql.org/docs/8.0/static/plpgsql.html
http://www.postgresql.org/docs/8.0/static/plpgsql-trigger.html
If you still have trouble then please post the PostgreSQL code you
tried, describe what you'd like to happen, and explain what actually
does happen.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] Trigger
@ 2005-02-17 09:29 Eugen Gass <gass@intend.de>
0 siblings, 0 replies; 14+ messages in thread
From: Eugen Gass @ 2005-02-17 09:29 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-admin@postgresql.org
Hi,
Thank you ALL for the fast help
it works fine with following code:
CREATE OR REPLACE FUNCTION synchronize () RETURNS trigger AS 'BEGIN
NEW.objectid := NEW.gid; RETURN NEW; END;' LANGUAGE plpgsql;
CREATE TRIGGER syncl_holz_lager AFTER INSERT ON holz_lagerplatz FOR EACH ROW
EXECUTE PROCEDURE synchronize();
Best Regards
Eugen
-----Ursprüngliche Nachricht-----
Von: Pavel Rabel [mailto:pavel@sajt.cz]
Gesendet: Mittwoch, 16. Februar 2005 21:43
An: Eugen Gass
Cc: pgsql-sql@postgresql.org
Betreff: Re: [SQL] Trigger
It requires a bit more work in PostgreSQL to create a trigger.
From the documentation: "It is not currently possible to write a
SQL-language trigger function. Trigger functions can be written in C or
in some of the available procedural languages."
I guess you will prefer to write the trigger in PL/pgSQL, have a look at
http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html
Regards
Pavel
Eugen Gass wrote:
>Hi,
>
>I'm trying to create a trigger on PostgreSQL
>
>it should be like an oracle(sql) sample code:
>
>create or replace trigger frei_polygon_sync
>after INSERT on frei_polygon
>Referencing NEW as newROW
>for each row
>Begin
> :newRow.objektid := :newRow.gid;
> :
>end;
>
>Can sombody help me to do the same on Postrgres
>
>Thanks
>
>Best Regards
>
>EG
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Trigger
@ 2024-04-10 14:58 Stefan Sturm <mailling@anrath.info>
0 siblings, 3 replies; 14+ messages in thread
From: Stefan Sturm @ 2024-04-10 14:58 UTC (permalink / raw)
To: pgsql-sql
Hello,
I switch to PostgreSQL 7.3 and now I try to make some Triggers. But the
triggers are differnt to Oracle. Where can I find a tutorial about using
and creating triggers in PostgreSQL 7.3
I just want to update to fields on instert and update with the system
time...
Perhaps someone can help me.
Greetings,
Stefan Sturm
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2024-04-10 14:58 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-14 14:11 Trigger Carolyn Lu Wong <carolyn@kss.net.au>
2000-07-14 15:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-09-06 20:27 Trigger Craig May <craig.may@s2.enthdimension.com.au>
2000-09-06 10:24 ` Mike Baroukh <mbaroukh@i-panema.fr>
2000-09-06 12:18 ` Chris Ryan <chris@greatbridge.com>
2000-09-06 12:40 ` Chris Ryan <chris@greatbridge.com>
2005-02-16 13:44 Trigger Eugen Gass <gass@intend.de>
2005-02-16 20:53 ` Pavel Rabel <pavel@sajt.cz>
2005-02-16 23:47 ` Michael Fuhr <mike@fuhr.org>
2005-02-17 09:29 Re: [SQL] Trigger Eugen Gass <gass@intend.de>
2024-04-10 14:58 Trigger Stefan Sturm <mailling@anrath.info>
2003-04-10 16:07 ` A.Bhuvaneswaran <bhuvansql@myrealbox.com>
2003-04-10 16:12 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-04-10 17:54 ` Richard Huxton <dev@archonet.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox