RE: passing table to store proc

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Ryan (Ryan_at_discussions.microsoft.com)
Date: 01/07/05 

Date: Fri, 7 Jan 2005 15:01:02 -0800

There is no real way to pass tables to a stored proc (SP).

Two potentialities that come to mind...
Can you create a table function that does most of you joins and logic, and
then just join the Inserted and/or Deleted to it? That way each of you
triggers would be much smaller, and the code not replicated?

The other route would be to insert from the inserted/deleted to a
intermediary table and then just have the SP join to that. The difficulty
with that, is that multiple triggers inserting into the same table, and
multiple instances of the SP running might cause some problems. But you
could work around this if the SP didn't have to be instant, by scheduling the
SP to run every X minutes/hours/whatever.

Ryan

Remember vague questions get vague answers...

"Zeng" wrote:

> I am trying to monitor a set of tables (containing data for multiple
> companies) for any update, inserted, or deletion using trigger, if there is
> such event happens to certain set of companies, I'll have to update another
> table. Basically I'm implementing data caching invalidation.
>
> The problem is, the triggers are the same for all tables but I can't share
> the code by extracting the common code and put it into the store procedure
> and have all triggers call the store proc. The reason is...there is no way
> to pass the inserted and deleted table to store procedure, does anybody have
> comment or suggestion that I can have all of the triggers share the same
> code? Thanks!!
>
>
>

Relevant Pages

  • Write conflict error after adding trigger to table
    ... I have inherited an Access adp that directly connects to SQL Server. ... that database. ... stored proc in the database window instead of through the form, ... is preventing me from rolling these triggers to production where other apps ...
    (microsoft.public.access.adp.sqlserver)
  • execute w/triggers returning 0 rows
    ... I added some triggers to an existing SQL Server database. ... I noticed in testing the triggers using their ad hoc query tool that inserting a row ... So I'm wondering if SQL Server returns multiple row counts in this type of situation or just the first one. ...
    (perl.dbi.users)
  • Re: Trigger invoking VB passing parameters
    ... It would probably be ideal to alter the application that's inserting into ... Table-A so that it does the lat / long lookup before the insert, ... take into account multiple rows being inserted at once, ... placing calls into binary executables under triggers is ...
    (microsoft.public.sqlserver.programming)
  • Re: Can this be done with a trigger?
    ... Or else your triggers fires on an update and ... inserting into the HIST table. ... automatically like BEFORE/AFTER triggers do. ... you also need to write the UPDATE dml for the main table here ...
    (comp.databases.oracle.misc)
  • Re: How do I read back autonumber after do an insert in asp.net
    ... Prevention is better that cure.. ... But at the same time if your not inserting 2 rows ... > It's not only triggers... ...
    (microsoft.public.dotnet.framework.aspnet)