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
|
Список | 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 по дате отправления: