Trigger won't execute when using sp with input parameters

Поиск
Список
Период
Сортировка
От Erik Dahlstrand
Тема Trigger won't execute when using sp with input parameters
Дата
Msg-id 4257D76A.3020107@ing.hj.se
обсуждение исходный текст
Ответы Re: Trigger won't execute when using sp with input parameters  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-novice
Hi!

I'm not sure if this has anything to do with the npgsql data provider.
I'm trying to implement tsearch2 functionality within a table.
Each time a row is inserted or updated the following trigger should
execute:

CREATE TRIGGER tsvectorupdate
 BEFORE INSERT OR UPDATE
 ON "public".object
 FOR EACH ROW
 EXECUTE PROCEDURE tsearch2(idxfti, header, description);

Insertions are made from an ASP.NET page using npgsql.

When using an ''INSER INTO' SQL statement the trigger is executing fine.
Also a stored procedure with "hard coded" values will fire the trigger.

myCommand.CommandText = "SELECT
insert_object('book',61,'ABC','DEF',110,'S','abc@123.com')";

But when using input parameters (and a output parameter) the trigger
won't execute.

myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "SELECT
insert_object(:db_table_destination,:category_id,:header,:description,:price,:type_of_ad,:person_id)";


Below is the stored procedure:

CREATE OR REPLACE FUNCTION insert_object("varchar", int4, "varchar",
"varchar", "numeric", "varchar", "varchar")
 RETURNS int4 AS
$BODY$DECLARE
   insertString text;
   id int4;

BEGIN
   insertString := 'INSERT INTO ' || _db_table_destination
                || ' (category_id, header, description, price,
created_on, type_of_ad, person_id) '
                || 'VALUES (' || quote_literal(_category_id) || ','
                || quote_literal(_header) || ','
                || quote_literal(_description) || ','
                || quote_literal(_price) || ','
                || 'DEFAULT' || ','
                || quote_literal(_type_of_ad) || ','
                || quote_literal(_person_id) || ');';

   --RAISE NOTICE '%', insertString;
   EXECUTE insertString;

   id := currval('object_id_seq');

   RETURN id;

END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION insert_object("varchar", int4, "varchar", "varchar",
"numeric", "varchar", "varchar") OWNER TO postgres;


Any ideas?
Kind regards, Erik


В списке pgsql-novice по дате отправления:

Предыдущее
От: Jake Stride
Дата:
Сообщение: Rules
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Trigger won't execute when using sp with input parameters