how-to - trigger function with argument / parameter

[djdevx]

Dear all PostgreSQL xperts!

Hi, I am newbie in PostgreSQL. I am currently developing an app that use PostgreSQL as a BackEnd Database.
Now I am having problem with the function trigger since last week.

Here's the case:

create table nr_tr (tr_type character(3), period character(6), nr_tr integer);
create table h_trsale(nr_tr integer, sale_dt datetime, desc text);
create table h_trbuy (nr_tr integer, sale_dt datetime, desc text);

The table: nr_tr -> holds the latest transaction number per transaction type
(tr_type) and per book period (period).
================================================== ==========================
records the latest number of the transaction for certain types and will generate a new record and starts the transaction number from #1 again in every period (yyyymm).
tr_type = 'SAL' -> Sales transaction.
tr_type = 'BUY' -> Purchase transaction.


table: h_trbuy -> header purchase transaction
table: h_trsale -> header sales transaction
===========================================
Let say the current book period is '200801' (January 2008).
In every NEW transaction, nr_tr will record new number of transaction based on nr_tr calculation (this mimics the nextval() in SEQUENCE).
If system enter the new Book period ie. Feb. 2008 ('200802'), the new transaction

number will starts all over again from number 1.

I try to code a 'SEQUENCE-alike' function trigger:

create or replace function gen_nrtr(character(3), character(6))
returns "trigger" as $gen_nrtr$
declare
tcTR_TYPE %1;
tcPERIOD %2;

begin
select * from nr_tr where tr_type = tcTR_TYPE AND PERIOD = tcPERIOD;
IF ROW_COUNT == 0 THEN
INSERT INTO nr_tr VALUES (tcTR_TYPE, tcPERIOD, 0);
END IF

UPDATE nr_tr
SET nr_tr = nr_tr + 1
WHERE nr_tr where tr_type = tcTR_TYPE AND PERIOD = tcPERIOD;
RETURN NULL;

END; $gen_nrtr$ LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION gen_nrtr() OWNER TO postgres;


But the system won't allow it b'coz arguments are not allowed in trigger.
Well, is there any other way to make this happen? If it's in FoxPro, there won't be

any problem with the trigger function that using the arguments.

Does anybody out there could help?

Thank's in advance...


Regards,



djDevX

[rski]

Dear all PostgreSQL xperts!

Hi, I am newbie in PostgreSQL. I am currently developing an app that use PostgreSQL as a BackEnd Database.
Now I am having problem with the function trigger since last week.

Here's the case:

create table nr_tr (tr_type character(3), period character(6), nr_tr integer);
create table h_trsale(nr_tr integer, sale_dt datetime, desc text);
create table h_trbuy (nr_tr integer, sale_dt datetime, desc text);

The table: nr_tr -> holds the latest transaction number per transaction type
(tr_type) and per book period (period).
================================================== ==========================
records the latest number of the transaction for certain types and will generate a new record and starts the transaction number from #1 again in every period (yyyymm).
tr_type = 'SAL' -> Sales transaction.
tr_type = 'BUY' -> Purchase transaction.


table: h_trbuy -> header purchase transaction
table: h_trsale -> header sales transaction
===========================================
Let say the current book period is '200801' (January 2008).
In every NEW transaction, nr_tr will record new number of transaction based on nr_tr calculation (this mimics the nextval() in SEQUENCE).
If system enter the new Book period ie. Feb. 2008 ('200802'), the new transaction

number will starts all over again from number 1.

I try to code a 'SEQUENCE-alike' function trigger:

create or replace function gen_nrtr(character(3), character(6))
returns "trigger" as $gen_nrtr$
declare
tcTR_TYPE %1;
tcPERIOD %2;

begin
select * from nr_tr where tr_type = tcTR_TYPE AND PERIOD = tcPERIOD;
IF ROW_COUNT == 0 THEN
INSERT INTO nr_tr VALUES (tcTR_TYPE, tcPERIOD, 0);
END IF

UPDATE nr_tr
SET nr_tr = nr_tr + 1
WHERE nr_tr where tr_type = tcTR_TYPE AND PERIOD = tcPERIOD;
RETURN NULL;

END; $gen_nrtr$ LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION gen_nrtr() OWNER TO postgres;


But the system won't allow it b'coz arguments are not allowed in trigger.
Well, is there any other way to make this happen? If it's in FoxPro, there won't be

any problem with the trigger function that using the arguments.

Does anybody out there could help?

Thank's in advance...


Regards,



djDevX

I'm sorry you wrote so much text so I did'n read it all, but who told you trigger can't have arguments see here
http://www.postgresql.org/docs/8.2/i...tetrigger.html
and here
http://www.postgresql.org/docs/8.2/i...l-trigger.html

was it helpful?