pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to build a TRIGGER in POSTGERSQL
4+ messages / 3 participants
[nested] [flat]
* How to build a TRIGGER in POSTGERSQL
@ 2001-06-18 14:35 Ilan Fait <ilan@iweb.com>
0 siblings, 0 replies; 4+ messages in thread
From: Ilan Fait @ 2001-06-18 14:35 UTC (permalink / raw)
To: pgsql-sql
Hi ,
I need some help in building trigger and information about sysdate
(System date), any help will be appreciate.
1) I need to build a trigger that every time I insert into the table
one of the columns will get +1 number.
I have it in ORACLE ( see below the create of the sequence and
the trigger) but how you can do it in PostGer SQL)
CREATE SEQUENCE AD_MNG_SYS_SEQ MINVALUE 1 MAXVALUE 999999 CYCLE;
CREATE TRIGGER AD_MNG_SYS_TRIG
BEFORE INSERT ON AD_MNG_SYS
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
BEGIN
Select AD_MNG_SYS_SEQ.NEXTVAL INTO :new.AMS_ID FROM Dual;
END;
/
2) what is equal to 'sysdate' (to get/put the system date in a
table) in PostGerSQL.
Thanks,
Ilan
^ permalink raw reply [nested|flat] 4+ messages in thread
* How to build a TRIGGER in POSTGERSQL
@ 2001-06-18 15:02 Ilan Fait <ilan@iweb.com>
0 siblings, 2 replies; 4+ messages in thread
From: Ilan Fait @ 2001-06-18 15:02 UTC (permalink / raw)
To: pgsql-sql
Hi ,
I need some help in building trigger and information about sysdate
(System date), any help will be appreciate.
1) I need to build a trigger that every time I insert into the table
one of the columns will get +1 number.
I have it in ORACLE ( see below the create of the sequence and
the trigger) but how you can do it in PostGer SQL)
CREATE SEQUENCE AD_MNG_SYS_SEQ MINVALUE 1 MAXVALUE 999999 CYCLE;
CREATE TRIGGER AD_MNG_SYS_TRIG
BEFORE INSERT ON AD_MNG_SYS
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
BEGIN
Select AD_MNG_SYS_SEQ.NEXTVAL INTO :new.AMS_ID FROM Dual;
END;
/
2) what is equal to 'sysdate' (to get/put the system date in a
table) in PostGerSQL.
Thanks,
Ilan
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to build a TRIGGER in POSTGERSQL
@ 2001-06-18 20:53 Alla <alla@sergey.com>
parent: Ilan Fait <ilan@iweb.com>
1 sibling, 0 replies; 4+ messages in thread
From: alla@sergey.com @ 2001-06-18 20:53 UTC (permalink / raw)
To: pgsql-sql
> CREATE SEQUENCE AD_MNG_SYS_SEQ MINVALUE 1 MAXVALUE 999999 CYCLE;
Stays pretty much the same
>
> CREATE TRIGGER AD_MNG_SYS_TRIG
> BEFORE INSERT ON AD_MNG_SYS
> REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
> BEGIN
> Select AD_MNG_SYS_SEQ.NEXTVAL INTO :new.AMS_ID FROM Dual;
> END;
> /
create function ad_mng_sys_proc()
returns opaque as '
begin
new.ams_id := nextval(''ad_mng_sys_seq'');
return new;
end;
' LANGUAGE 'plpgsql';
create trigger ad_mng_sys_trig
before insert on ad_mng_sys
for each row
execute procedure ad_mng_sys_proc();
> 2) what is equal to 'sysdate' (to get/put the system date in a
> table) in PostGerSQL.
now()
Enjoy :-))
Alla Gribov
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to build a TRIGGER in POSTGERSQL
@ 2001-06-19 11:43 D'Arcy J.M. Cain <darcy@druid.net>
parent: Ilan Fait <ilan@iweb.com>
1 sibling, 0 replies; 4+ messages in thread
From: darcy@druid.net @ 2001-06-19 11:43 UTC (permalink / raw)
To: Ilan Fait <ilan@iweb.com>; +Cc: pgsql-sql
Thus spake Ilan Fait
> 1) I need to build a trigger that every time I insert into the table
> one of the columns will get +1 number.
The easiest way in PostgreSQL is to simply make the field a serial type.
CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
myname TEXT);
> 2) what is equal to 'sysdate' (to get/put the system date in a
> table) in PostGerSQL.
Again, the table definition can handle this.
CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
mydate DATE DEFAULT CURRENT_DATE,
myname TEXT);
You should check out the docs on the web site. They are quite comprehensive.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-06-19 11:43 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-18 14:35 How to build a TRIGGER in POSTGERSQL Ilan Fait <ilan@iweb.com>
2001-06-18 15:02 How to build a TRIGGER in POSTGERSQL Ilan Fait <ilan@iweb.com>
2001-06-18 20:53 ` Alla <alla@sergey.com>
2001-06-19 11:43 ` D'Arcy J.M. Cain <darcy@druid.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox