Post reply

Run sql when a stored procedure is created

  • October 6, 2004 at 1:18 pm

    #161644

    I would like to run some sql when a new stored procedure is created in a DB. Is there an easy way to do this. I tried a trigger on the sysobjects table but apparently that is not possible.

    Thanks

  • October 6, 2004 at 3:04 pm

    #525649

    Let's see, this is from the hip, so to speak. Any you probably thought of this already too.

    1) create a log table of object ids and object names and load it with the objects you want to check (stored procedures for this but could be any object)

    2) create a job to run, say, every ten minutes - in this job look for the new objects in sysobects that are not in your log table

    3) report on the created objects.

    This should work and is the basic process we use over and over.

  • October 6, 2004 at 3:09 pm

    #525651

    I thought of that, but the scheduled execution is not an option. We have some very specific security definitions on our Stored Procedures and I need to set a bunch of access rights when a proc is created based on some criteria stored in another table. This has to happen as soon as the Proc is created.... Any other ideas?

    Thanks for the feedback,

    Mike

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply