|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Deleting a record dynamicallyI have some tables and their copy: RHUM010P and ##RHUM010P_Inserted which is basically a copy of the inserted record in RHUM010P Now, for each record in ##RHUM010P I need to delete each and every records who are presents in ##RHUM010P_Inserted The problem is: In ##RHUM010P I have the complete record (and the key) to find the record to delete in RHUM010P however, the store procedure that do this isn't dependent on the RHUM010P structure... so I can have to delete records from any table pair (i.e: RHUM030P and ##RHUM030P_Inserted for instance) Since I can "hardcode" a key in my stored proc, I need a way to locate (to delete) those records in the basic tables. If I use a cursor, I'll have to declare a variable for every fields in the key which can be error prone and difficult. Any ideas? Michel Racicot wrote:
Show quoteHide quote > Hi there... Why do you need a single proc to delete rows dynamically from different> > I have some tables and their copy: > > RHUM010P and ##RHUM010P_Inserted which is basically a copy of the inserted > record in RHUM010P > > Now, for each record in ##RHUM010P I need to delete each and every records > who are presents in ##RHUM010P_Inserted > > The problem is: > > In ##RHUM010P I have the complete record (and the key) to find the record to > delete in RHUM010P however, the store procedure that do this isn't dependent > on the RHUM010P structure... so I can have to delete records from any table > pair (i.e: RHUM030P and ##RHUM030P_Inserted for instance) > > Since I can "hardcode" a key in my stored proc, I need a way to locate (to > delete) those records in the basic tables. > > If I use a cursor, I'll have to declare a variable for every fields in the > key which can be error prone and difficult. > > Any ideas? tables? It sounds like you have some serious issues with your design. Stick to ONE table (or view) per entity type and ONE proc for each distinct operation. The problem you are having should not arise. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- This stored procedure recieve in parameters the name of the table that needs
to be processed. The reason for a single stored procedure, is that this can happen with any of our table in our application. We don't want to create a procedure for each and every table. Especially since more tables might be added in the future. Show quoteHide quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1158177884.896738.75860@p79g2000cwp.googlegroups.com... > Michel Racicot wrote: >> Hi there... >> >> I have some tables and their copy: >> >> RHUM010P and ##RHUM010P_Inserted which is basically a copy of the >> inserted >> record in RHUM010P >> >> Now, for each record in ##RHUM010P I need to delete each and every >> records >> who are presents in ##RHUM010P_Inserted >> >> The problem is: >> >> In ##RHUM010P I have the complete record (and the key) to find the record >> to >> delete in RHUM010P however, the store procedure that do this isn't >> dependent >> on the RHUM010P structure... so I can have to delete records from any >> table >> pair (i.e: RHUM030P and ##RHUM030P_Inserted for instance) >> >> Since I can "hardcode" a key in my stored proc, I need a way to locate >> (to >> delete) those records in the basic tables. >> >> If I use a cursor, I'll have to declare a variable for every fields in >> the >> key which can be error prone and difficult. >> >> Any ideas? > > Why do you need a single proc to delete rows dynamically from different > tables? It sounds like you have some serious issues with your design. > Stick to ONE table (or view) per entity type and ONE proc for each > distinct operation. The problem you are having should not arise. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > Michel Racicot wrote:
> We don't want to create a procedure for each and every table. Especially You don't necessarily have to. But if each entity is represented by> since more tables might be added in the future. only one table it is hard to see why you would expect new tables to exist without corresponding new logic, business processes, user-interface etc. The proc to delete from the tables is just one part of that logic. Usually this type of proc is generated semi-automatically so the effort to maintain it is virtually zero. Dynamic procs are highly undesirable for all sorts of reasons. What kind of application is this and why don't you know what tables you have in it? -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- The temporary tables are created by all three "before" triggers (insert,
delete and modify) with records in them to help reverting the changes instead of using the regular begin transaction/commit/rollback functionnalities of MSSQL. The list of all tables created in this way is kept in another table (##Rollback_List) All temporary tables are globals since local temporary tables created in triggers are not seen by stored procedures called by the triggers. I didn't mention it before but each temporary tables names also contain the name of the user that created them. If my rollback should occur, I want to revert the changes to their previous status by browsing the ##Rollback_List table to find each tables that contains informations to be rollbacked. The reason why I do all this is because our database contains multiples companies and an import process (which can take a long time for a huge number of records) can be runned in a company at any moment and it blocks other companies in the same database (row locking prevent "select *" statements from completing... not to mention page locks and table locks...!) Our application also use Interbase for some clients... and in Interbase we don't have this problem since "select *" by other users in Interbase can perform well in a transaction context... So we wanted to have the same behavior as we do in Interbase. Show quoteHide quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1158185719.948075.280590@e63g2000cwd.googlegroups.com... > Michel Racicot wrote: >> We don't want to create a procedure for each and every table. Especially >> since more tables might be added in the future. > > You don't necessarily have to. But if each entity is represented by > only one table it is hard to see why you would expect new tables to > exist without corresponding new logic, business processes, > user-interface etc. The proc to delete from the tables is just one part > of that logic. > > Usually this type of proc is generated semi-automatically so the effort > to maintain it is virtually zero. Dynamic procs are highly undesirable > for all sorts of reasons. What kind of application is this and why > don't you know what tables you have in it? > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > Michel Racicot (mraci***@hotmail.com) writes:
> All temporary tables are globals since local temporary tables created in Eh? A local temp table created in one scope is visible to all inner> triggers are not seen by stored procedures called by the triggers. scopes called from that scope. That is, if you create temp table a trigger and call a procedure from that trigger, the procedure can access the table. > The reason why I do all this is because our database contains multiples With appropriate indexing this could possibly be avoided. At least > companies and an import process (which can take a long time for a huge > number of records) can be runned in a company at any moment and it > blocks other companies in the same database (row locking prevent "select > *" statements from completing... not to mention page locks and table > locks...!) under the assumption that these SELECT * are not cross-company. (By the way, SELECT * is not a good thing in application code.) I would certainly look into improvements in that area, rather than trying to roll my own transaction handling. That sounds very expensive in terms of man-hours, and it can be very difficult to make fool-proof. If you are on SQL 2005, snapshot isolation may be what you need. If you are not on SQL 2005, it may be a good idea to move there. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Michel Racicot (mraci***@hotmail.com) writes:
> This stored procedure recieve in parameters the name of the table that In a properly designed database, each table descritbes a unique entity.> needs to be processed. > > The reason for a single stored procedure, is that this can happen with any > of our table in our application. > > We don't want to create a procedure for each and every table. Especially > since more tables might be added in the future. Thus, two tables are only similar to each other in structure by chance. From this follows that stored procedures that deals with different tables are also only similar by chance. In this case, I don't even understand why you have a procedure and a global temp table. Just delete the rows in the trigger. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx This seems like a great opportunity for a cascading delete...
Here is the best article... http://support.microsoft.com/default.aspx?scid=kb;en-us;142480 http://www.4guysfromrolla.com/webtech/sqlguru/q061400-1.shtml Let me know if those articles will help you with your problem... thanks, Show quoteHide quote "Michel Racicot" <mraci***@hotmail.com> wrote in message news:%23SvU7p21GHA.328@TK2MSFTNGP06.phx.gbl... > Hi there... > > I have some tables and their copy: > > RHUM010P and ##RHUM010P_Inserted which is basically a copy of the inserted > record in RHUM010P > > Now, for each record in ##RHUM010P I need to delete each and every records > who are presents in ##RHUM010P_Inserted > > The problem is: > > In ##RHUM010P I have the complete record (and the key) to find the record > to delete in RHUM010P however, the store procedure that do this isn't > dependent on the RHUM010P structure... so I can have to delete records > from any table pair (i.e: RHUM030P and ##RHUM030P_Inserted for instance) > > Since I can "hardcode" a key in my stored proc, I need a way to locate (to > delete) those records in the basic tables. > > If I use a cursor, I'll have to declare a variable for every fields in the > key which can be error prone and difficult. > > Any ideas? >  
Other interesting topics
Little bug in sql2005?
Need Consecutive Query Different result from Script and SP Set Database Context inside a stored proc What am I missing with this simple query? Passing username to sql server from an app SQL search for similar records with different dates For insert trigger problem Table Design Joined Query With Optional Data |
|||||||||||||||||||||||
