Postgres Pro
Downloads
Home   >   mailing lists

Thread: Trigger for Truncate event

Trigger for Truncate event

From
Jignesh Shah
Date:
Hi,
 
Could any one please point me to TRUNCATE trigger PostgreSQL example?
 
Thanks,
Jignesh

Re: Trigger for Truncate event

From
"A. Kretschmer"
Date:
In response to Jignesh Shah :
> Hi,
>  
> Could any one please point me to TRUNCATE trigger PostgreSQL example?
>  
> Thanks,

test=# create table jignesh (i int);
CREATE TABLE
test=*# create function trg_jignesh() returns trigger as $$begin raise notice 'in the function'; return new; end;$$
languageplpgsql; 
CREATE FUNCTION
test=*# create trigger trg1 before truncate on jignesh for each statement execute procedure trg_jignesh();
CREATE TRIGGER
test=*# create trigger trg2 after truncate on jignesh for each statement execute procedure trg_jignesh();
CREATE TRIGGER
test=*# truncate jignesh ;
NOTICE:  in the function
NOTICE:  in the function
TRUNCATE TABLE


ROLLBACK, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: Trigger for Truncate event

From
Jignesh Shah
Date:
Thanks Kretschmer. Could you tell me what "return new" meant for?
 
Thanks,
Jigensh

On Thu, Aug 27, 2009 at 12:47 PM, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
In response to Jignesh Shah :
> Hi,
>  
> Could any one please point me to TRUNCATE trigger PostgreSQL example?
>  
> Thanks,

test=# create table jignesh (i int);
CREATE TABLE
test=*# create function trg_jignesh() returns trigger as $$begin raise notice 'in the function'; return new; end;$$ language plpgsql;
CREATE FUNCTION
test=*# create trigger trg1 before truncate on jignesh for each statement execute procedure trg_jignesh();
CREATE TRIGGER
test=*# create trigger trg2 after truncate on jignesh for each statement execute procedure trg_jignesh();
CREATE TRIGGER
test=*# truncate jignesh ;
NOTICE:  in the function
NOTICE:  in the function
TRUNCATE TABLE


ROLLBACK, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: Trigger for Truncate event

From
"A. Kretschmer"
Date:
In response to Jignesh Shah :
> Thanks Kretschmer. Could you tell me what "return new" meant for?

A trigger function must return either NULL or a record/row value having
exactly the structure of the table the trigger was fired for. Okay, in
this case, you can also return NULL. NEW means the new record, but you
need it only for row level triggers afaik.


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: Trigger for Truncate event

From
Jignesh Shah
Date:
Thanks Andreas, when I tried to create trigger it gives me error. Could you tell me what is wrong?
 
mydb=# CREATE TRIGGER truncate_trigger
mydb-# AFTER TRUNCATE ON mytable
mydb-# FOR EACH STATEMENT EXECUTE PROCEDURE my_test();
ERROR:  syntax error at or near "TRUNCATE" at character 52
LINE 2: AFTER TRUNCATE ON mytable
Thanks,
Jignesh

On Thu, Aug 27, 2009 at 1:47 PM, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
In response to Jignesh Shah :
> Thanks Kretschmer. Could you tell me what "return new" meant for?

A trigger function must return either NULL or a record/row value having
exactly the structure of the table the trigger was fired for. Okay, in
this case, you can also return NULL. NEW means the new record, but you
need it only for row level triggers afaik.


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: Trigger for Truncate event

From
Thomas Kellerer
Date:
Jignesh Shah wrote on 27.08.2009 19:37:
> Thanks Andreas, when I tried to create trigger it gives me error. Could
> you tell me what is wrong?
>
> mydb=# CREATE TRIGGER truncate_trigger
> mydb-# AFTER TRUNCATE ON mytable
> mydb-# FOR EACH STATEMENT EXECUTE PROCEDURE my_test();
> ERROR:  syntax error at or near "TRUNCATE" at character 52
> LINE 2: AFTER TRUNCATE ON mytable

You are probably using a version prior to 8.4

You need 8.4 to create triggers for TRUNCATE

Thomas

Re: Trigger for Truncate event

From
Jignesh Shah
Date:
Hmm..you are correct.
 
Thanks.

On Thu, Aug 27, 2009 at 11:20 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
Jignesh Shah wrote on 27.08.2009 19:37:

Thanks Andreas, when I tried to create trigger it gives me error. Could you tell me what is wrong?
 mydb=# CREATE TRIGGER truncate_trigger
mydb-# AFTER TRUNCATE ON mytable
mydb-# FOR EACH STATEMENT EXECUTE PROCEDURE my_test();
ERROR:  syntax error at or near "TRUNCATE" at character 52
LINE 2: AFTER TRUNCATE ON mytable

You are probably using a version prior to 8.4

You need 8.4 to create triggers for TRUNCATE

Thomas


--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice