pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How 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>
  2001-06-18 20:53 ` Re: How to build a TRIGGER in POSTGERSQL Alla <alla@sergey.com>
  2001-06-19 11:43 ` Re: How to build a TRIGGER in POSTGERSQL D'Arcy J.M. Cain <darcy@druid.net>
  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 15:02 How to build a TRIGGER in POSTGERSQL Ilan Fait <ilan@iweb.com>
@ 2001-06-18 20:53 ` Alla <alla@sergey.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-18 15:02 How to build a TRIGGER in POSTGERSQL Ilan Fait <ilan@iweb.com>
@ 2001-06-19 11:43 ` D'Arcy J.M. Cain <darcy@druid.net>
  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