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