Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

error when running trigger (TG_OP not recognized) 1

Status
Not open for further replies.

gacaccia

Technical User
May 15, 2002
258
0
0
US
hi all. i'm trying to write a generic trigger function that can be called by any trigger belonging to any table. here's what i've got...

Code:
CREATE OR REPLACE FUNCTION process_table_activity_details()
  RETURNS "trigger" AS
$BODY$
DECLARE
	table_ident varchar(100);
	strsql varchar(1000);
	actionSet varchar(50);

BEGIN
	table_ident = 'qa.' || TG_TABLE_NAME || '_audit';
	IF (TG_OP = 'DELETE') THEN
		actionSet = 'OLD.*';
	ELSE
		actionSet = 'NEW.*';
	END IF;

	IF EXISTS (select * from all_qa_tables where table_name = table_ident) THEN
		strsql = 'insert into ' || table_ident || ' select now(), TG_OP, quote_ident(actionSet)';
	ELSE
		strsql = 'select now(), TG_OP, quote_ident(actionSet) into ' || table_ident;
	END IF;

	execute strsql;

        RETURN NULL; 
    END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION process_table_activity_details() OWNER TO postgres;

when i run a query that activates a trigger that calls the above function, i get an error that "column tg_op does not exist". i know that postgresql is case sensitive, so i assume it is reporting an error because the function is converting "TG_OP" into lower case when passing it in as a string to EXECUTE. any ideas on how to get around this problem?
 
Maybe just put TG_OP outside the quots
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top