Saturday, February 25, 2012

Pass a parameter to a trigger?

The application developers came up with an interesting requirement that Id like some help with.

They want to be able to somehow pass a parameter to the trigger that is fired when you do something to a table. The parameter would control what logic is executed within the trigger.

The two solutions that I proposed, but were found unacceptable by them were:
1. Have parameter as a column within the table itself.
2. Dont use Update, Delete, Insert but instead use a stored procedure.

Thoughts are welcome.

Thanks.As an alternative to #1 you can have a dedicated parameter table that needs to acquire a control value before the action query is issued. But I would choose #2 any time over anything else. What was the argument your developers used to dismiss your #2?|||The bias against the stored procedure was that they are using a VB .NET code generator that creates the "behind the scenes" SQL statements based upon their screen construction. It's cumbersome (read that as extra work!) for them to model screens against stored procedures - especially if they had to write them themselves.|||I guess programmers become lazier and lazier. So they want a trigger that "takes" a parameter rather than having a stored procedure that does take a parameter? Would they like for the trigger to return resultset for them too? I bet they show up to work by 11:30AM and stay up all night playing Unreal Tournament too ;)|||I would also like to know whether there is a way to pass parameter to a trigger. Does anyone know how to?
Its not the question whether programmers are lazy or not. Sometimes they run into timelines issue or they may not be able to change the design of the application.|||no, no, no. programmers are lazy. Is that you Jawad?|||There is only one way I know to "pass a paramter" to a Trigger...but it's not really a parameter, but more a select from sysprocesses, which would be dangerous with these developes, so the sort answer is no you can't

Well as cumbersome as it is to read a sproc, what about the complexity of dynamically telling a trigger what to do?

You got a for instance?

Why not create them Views that contain the business logic they want to implement. AND restrict their access to those views.

You seriously are going to have a problem with the guys..

Can you imagine...developers telling a dba what to do...

What terrible times we are living in...|||A very simple and elegant solution is ... coming from the COBOL times ... Back then it used to be called a parameter file (of course that was an invention of lazy and not very bright COBOL developers that had no earthly idea about LINKAGE section). Now, we live in times of lazy SQL developers, but the brilliance of parameter file/table seems to shine bright ;)

No comments:

Post a Comment