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 Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

function that creates triggers

Status
Not open for further replies.

gacaccia

Technical User
May 15, 2002
258
US
i want to create a function that will add a trigger to every user table in a database. i tried the following, but it gives me an error nearw keyword "trigger". is what i'm trying to do possible or is the "execute" statement not able to run "create" statements?

Code:
CREATE OR REPLACE FUNCTION createtriggers()
  RETURNS void AS
$BODY$
DECLARE
	tables CURSOR for select table_name from alltables;
	table_name alltables.table_name%type;
	strsql varchar(1000);

BEGIN
	open tables;
	loop
		fetch tables into table_name;
		if not found then
			exit;
		end if;
		strsql = 'CREATE OR REPLACE TRIGGER  ' || table_name || '_stamp AFTER INSERT OR UPDATE OR DELETE ON  ' || table_name || ' FOR EACH ROW EXECUTE PROCEDURE process_table_stamp()';
		execute strsql;
	end loop;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
 
never mind. figured it out. didn't realize that you can't use the REPLACE keyword with triggers. removed that and it works great.
 
i dont know u are aware but
u have forget
fetch next
command. the loop may be endless.
 
the code works as expected, so it doesn't appear that "fetch next" is needed, which does beg the question what does "fetch next" do that is different from just plain old "fetch"? i did a quick search and found examples of other people using the same syntax (just using plain old "fetch").
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top