Обсуждение: trigger not firing

Поиск
Список
Период
Сортировка

trigger not firing

От
joseph speigle
Дата:
hello novice list,
can somebody show me where my mistake is with this trigger.
everything is fine, but they don't fire......

[postgres@www ~]$ pg_ctl --version
pg_ctl (PostgreSQL) 8.1.3
[postgres@www ~]$

---------------------------------------------------------------------
drop function qp_question_moddate() cascade ;

CREATE FUNCTION  qp_question_moddate()  RETURNS  OPAQUE
AS '
BEGIN

  NEW.moddate := current_date;
  RETURN  NEW;
END;
' LANGUAGE 'plpgsql';

create trigger qp_question_moddate_update after update on qp_question for each row execute procedure
qp_question_moddate();

drop function qp_question_addate() cascade ;

CREATE FUNCTION  qp_question_addate()  RETURNS  OPAQUE
AS '
BEGIN

  NEW.addate := current_date;
  RETURN  NEW;
END;
' LANGUAGE 'plpgsql';

create trigger qp_question_addate_insert after insert on qp_question for each row execute procedure
qp_question_addate();

---------------------------------------------------------
database1=> \e me
NOTICE:  drop cascades to trigger qp_question_moddate_update on table qp_question
DROP FUNCTION
CREATE FUNCTION
WARNING:  changing return type of function qp_question_moddate from "opaque" to "trigger"
CREATE TRIGGER
NOTICE:  drop cascades to trigger qp_question_addate_insert on table qp_question
DROP FUNCTION
CREATE FUNCTION
WARNING:  changing return type of function qp_question_addate from "opaque" to "trigger"
CREATE TRIGGER
database1=>


database1=> \d qp_question;
                                     Table "modpgwebuser.qp_question"
    Column    |          Type          |                             Modifiers
--------------+------------------------+-------------------------------------------------------------------
<snip>

 moddate      | date                   |
 addate       | date                   |

<snip>

Triggers:
    qp_question_addate_insert AFTER INSERT ON qp_question FOR EACH ROW EXECUTE PROCEDURE qp_question_addate()
    qp_question_moddate_update AFTER UPDATE ON qp_question FOR EACH ROW EXECUTE PROCEDURE qp_question_moddate()


--------------------------------------------------------------------------------------
mod=> select * from qp_question;
 qid |    type    |    questiontext     | dependency | qualifying | indent |  moddate   |   addate   | battery_id
-----+------------+---------------------+------------+------------+--------+------------+------------+------------
 278 | break      | break               |            |            |      0 | 2004-08-19 | 2004-08-19 | klpt4
 279 | radio      | choose first answer |            |            |      0 |            | 2004-08-22 | klpt4
 282 | break      | break               |            |            |      0 | 2004-08-22 | 2004-08-22 | klpt4
   2 | selectDate | change,edit,first   |            |            |      0 | 2004-08-19 | 2004-08-19 | sdfg
 199 | selectDate | change,edit,first   |            |            |      0 | 2004-08-19 | 2004-08-19 | sdfg
   1 | selectDate | arx                 |            |            |      0 | 2004-08-19 | 2004-08-19 | klpt4
(6 rows)

mod=> update qp_question set questiontext = 'arx' where qid=1;
UPDATE 1
mod=> select * from qp_question;
 qid |    type    |    questiontext     | dependency | qualifying | indent |  moddate   |   addate   | battery_id
-----+------------+---------------------+------------+------------+--------+------------+------------+------------
 278 | break      | break               |            |            |      0 | 2004-08-19 | 2004-08-19 | klpt4
 279 | radio      | choose first answer |            |            |      0 |            | 2004-08-22 | klpt4
 282 | break      | break               |            |            |      0 | 2004-08-22 | 2004-08-22 | klpt4
   2 | selectDate | change,edit,first   |            |            |      0 | 2004-08-19 | 2004-08-19 | sdfg
 199 | selectDate | change,edit,first   |            |            |      0 | 2004-08-19 | 2004-08-19 | sdfg
   1 | selectDate | arx                 |            |            |      0 | 2004-08-19 | 2004-08-19 | klpt4
(6 rows)

mod=> insert into qp_question (type,questiontext) values ('break','somethign');
INSERT 0 1
mod=> select * from qp_question;
 qid |    type    |    questiontext     | dependency | qualifying | indent |  moddate   |   addate   | battery_id
-----+------------+---------------------+------------+------------+--------+------------+------------+------------
 278 | break      | break               |            |            |      0 | 2004-08-19 | 2004-08-19 | klpt4
 279 | radio      | choose first answer |            |            |      0 |            | 2004-08-22 | klpt4
 282 | break      | break               |            |            |      0 | 2004-08-22 | 2004-08-22 | klpt4
   2 | selectDate | change,edit,first   |            |            |      0 | 2004-08-19 | 2004-08-19 | sdfg
 199 | selectDate | change,edit,first   |            |            |      0 | 2004-08-19 | 2004-08-19 | sdfg
   1 | selectDate | arx                 |            |            |      0 | 2004-08-19 | 2004-08-19 | klpt4
 283 | break      | somethign           |            |            |      0 |            |            | survey
(7 rows)


~


--
joe speigle
www.sirfsup.com

Re: trigger not firing

От
Stephan Szabo
Дата:
On Mon, 26 Feb 2007, joseph speigle wrote:

>
> hello novice list,
> can somebody show me where my mistake is with this trigger.
> everything is fine, but they don't fire......
>
> [postgres@www ~]$ pg_ctl --version
> pg_ctl (PostgreSQL) 8.1.3
> [postgres@www ~]$
>
> ---------------------------------------------------------------------
> drop function qp_question_moddate() cascade ;
>
> CREATE FUNCTION  qp_question_moddate()  RETURNS  OPAQUE
> AS '
> BEGIN
>
>   NEW.moddate := current_date;
>   RETURN  NEW;
> END;
> ' LANGUAGE 'plpgsql';
>
> create trigger qp_question_moddate_update after update on qp_question
> for each row execute procedure qp_question_moddate();

After triggers are too late for modifying NEW (since the record is already
created or changed), you'd need to make it a before trigger.

Re: trigger not firing

От
joseph speigle
Дата:
> >
> > hello novice list,
> > can somebody show me where my mistake is with this trigger.
> > everything is fine, but they don't fire......
> >
> > ---------------------------------------------------------------------
> >
> > CREATE FUNCTION  qp_question_moddate()  RETURNS  OPAQUE
> > AS '
> > BEGIN
> >
> >   NEW.moddate := current_date;
> >   RETURN  NEW;
> > END;
> > ' LANGUAGE 'plpgsql';
> >
> > create trigger qp_question_moddate_update after update on qp_question
> > for each row execute procedure qp_question_moddate();
>
> After triggers are too late for modifying NEW (since the record is already
> created or changed), you'd need to make it a before trigger.


great, thank you

--
joe speigle
www.sirfsup.com