×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

function that creates triggers

function that creates triggers

function that creates triggers

(OP)
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;

RE: function that creates triggers

(OP)
never mind.  figured it out.  didn't realize that you can't use the REPLACE keyword with triggers.  removed that and it works great.

RE: function that creates triggers

i dont know u are aware but
u have forget
fetch next
command. the loop may be endless.

RE: function that creates triggers

(OP)
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").

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close