stored procedure to perform a update

[Edimator]

Hi!!!!, I have a coldfusion server conected to a SQLServer and I need to perform a few sql statements before each update in any table. For example, I wish to save

a)the user that perform the update
b)the older value and the new value for each field changed
c)every field changed.

I create a stored procedure in sql that perform this, but i don't know how to call the procedure from a update_page.cfm .

If anybody have a idea or where i can find more info, i really apreciate.

 

[Pahan]

I have exact same problem! I wish someone answered this question.

 

[schase]

I have never used ColdFusion - nor SQL.

However having said that, If I were to do this in ASP/Access.

I would do the following.

A) create a hidden field with the value being the person who's logged in. (cookie, session, etc.)
<Input type=&quot;hidden&quot; name=&quot;fldUser&quot; value=&quot;<%=Session(&quot;svUserName&quot%>&quot;>

B)Do the same as A - but duplicate fields in your database so you may have database field name fldTitle, make an additional one call it fldTitle_Old. put the old value into a hiddenfield, and when you update, update that value to fldTitle_Old, while the new one goes into the normal slot.

C) Every field changed. Well i'm guessing there would only be a few right? You could also do a hidden field with the value of date() so you can pull reports, showing old and new values.

&quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart

 

[Pahan]

Schase, I am not sure if Edimator's problem is exactly the same as my, but the question I have is - how does one call/executes the SQL or PL/SQL procedure stored in the database from a Cold Fusion (.cfm in my case) page?
I know how to create a recordset with SQL and needed parameters, but I do not want to use it because whether I will do the update or the insert will depend on what I already have in the database, which means I would have to use another &quot;SELECT&quot; recordset. I do not want to query the databse, make the decision and then update or insert from the cfm page - that would be too slow. So I created the PL/SQL procedure in Oracle database which takes two parameters and performs the update/insert; there is no output needed. So, once again, all I need is to call this procedure, pass two parameters to it and let it do its magic. How? Please.

 

[schase]

Looks like your outta my scope. I offered suggestions with hope that it could trigger some ideas.

I know there is a SQL forum here at Tek-Tips That would probably give you the best opportunity for a rapid response. &quot;Damn the torpedoes, full speed ahead!&quot;

-Adm. James Farragut

Stuart