Thread: How to build a TRIGGER in POSTGERSQL

<br /><br /><p><font face="Arial" size="2">       Hi ,</font><p><font face="Arial" size="2">      I need some help in 
building trigger and information about sysdate (System date), any help will be appreciate.  </font><p><font
face="Arial"size="2">      1) I need to build a trigger that every time I insert into the table  one of the columns
will get +1 number.</font><p><font face="Arial" size="2">           I have it in ORACLE ( see below the create of the
sequenceand the trigger) but how you can do it in PostGer SQL) </font><p><font face="Arial" size="2">  </font><p><font
face="Arial"size="2">   CREATE SEQUENCE AD_MNG_SYS_SEQ MINVALUE 1 MAXVALUE 999999 CYCLE;</font><p><font face="Arial"
size="2">   CREATE TRIGGER AD_MNG_SYS_TRIG</font><br /><font face="Arial" size="2">    BEFORE INSERT ON
AD_MNG_SYS</font><br/><font face="Arial" size="2">   REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW</font><br /><font
face="Arial"size="2">   BEGIN</font><br /><font face="Arial" size="2">   Select AD_MNG_SYS_SEQ.NEXTVAL INTO :new.AMS_ID
FROMDual;</font><br /><font face="Arial" size="2">  END;</font><br /><font face="Arial" size="2">   /</font><br
/><p><fontface="Arial" size="2">        2) what is  equal to 'sysdate' (to get/put the system date in a table)  in
PostGerSQL.</font><br/><br /><p><font face="Arial" size="2">     Thanks,</font><p><font face="Arial" size="2">    
Ilan</font>

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.

>    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