SQL Triggers on eProcedure

We're trying to internally monitor our environment for changes... especially some crafty developer that may want to publish in the wee hours and not tell anyone. Mostly it's through an abundance of caution.

My suggestion was to create a SQL Trigger on INSERT into eProcedure. That raises a flag that starts a process to figure out what got added, comments, etc and email a specific group of senior developers.

But does this pose any overhead to the MBPM environment? eProcedure isn't really an active table. I assume once you publish then the Engine loads the procedure into memory and it runs from there... that eProcedure is just a place to persist the procedure itself... either way, I don't see the risk, but I want to cover my bases.

Thanks,

Paul

Find more posts tagged with

BPM

Comments

McGaw_Phil

Hi Paul,

I'm not a massive fan of triggers in general solution implementations, but for your private eye task I think putting a trigger on eProcedure should be fine, as I don't think it is used after the publish. (I just did a quick SQL Profiler trace clicking around an environment and couldn't see any sql queries hitting it).

The eProcedure table can become a bit of a beast to query after you throw a lot of solutions at it and I'm not sure if deployment performance will be affected by it or not, so if you wanted an alternative to a trigger on eProcedure, you could put a trigger on eLog and include a condition in the trigger to only fire when the eComponentType column equals 'Designer'.

see: SELECT * FROM dbo.eLog WHERE eComponentType = 'Designer'

Conditional Triggers Example

http://stackoverflow.com/questions/280904/trigger-to-fire-only-if-a-condition-is-met-in-sql-SERVER

Good luck in catching the secret coder.

Tony Wheeler

I generally wouldn’t recommend changing anything in the database unless it specifically supports a process (stored procs, views, functions). Triggers, especially on system tables, can be problematic because they can be difficult to track down when strange things start happening. I would instead suggest enforcing organizational change control by limiting deployment/access permissions on shared dev environments.

McGaw_Phil

I'm guessing that once you had some evidence on the outlaw you would remove the trigger though, so as a temporary measure to bring the villain to justice, the trigger is a reasonable "technical" solution.