|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
INSTEAD OF INSERT triggerIs it correct to write this way? Does it mean that the trigger will not call
itself as a recurse? I wrote a INSTEAD OF INSERT trigger working with the same table where it's stored, the trigger checks if the record with same properties already exists in this table and just replaces INSERT with UPDATE against the same table if the record exists to avoid duplications. Does it mean that when the trigger initiates INSERT the same trigger INSTEAD OF INSERT will not be called? And if so how can we avoid this situation? Also how can we work with the "inserted" row by row? Do we have something like FOR EACH ROW in SQL Server 2000 to use it in the triggers? Just D. Just,
What you're refering to is Recursive Triggers which are disabled by default. Is it possible for you to use a PK or UNIQUE constraint instead of a trigger to maintain entity integrity? More than likely a better approach. HTH Jerry "Just D." <no@spam.please> wrote in message news:Zf2Ze.147058$Ji4.13923@fed1read03...Show quote > Is it correct to write this way? Does it mean that the trigger will not > call itself as a recurse? > > I wrote a INSTEAD OF INSERT trigger working with the same table where it's > stored, the trigger checks if the record with same properties already > exists in this table and just replaces INSERT with UPDATE against the same > table if the record exists to avoid duplications. Does it mean that when > the trigger initiates INSERT the same trigger INSTEAD OF INSERT will not > be called? And if so how can we avoid this situation? > > Also how can we work with the "inserted" row by row? Do we have something > like FOR EACH ROW in SQL Server 2000 to use it in the triggers? > > Just D. > > Hi Jerry,
Yes, I'm actually using the PK inside this trigger to understand if the inserted record already exists to replace INSERT by UPDATE with the same fields, but I can't understand if it's applied to the whole INSERTED as a table or works row by row? I'd prefer a second way but I don't know how to do that, because in most cases many rows should be inserted and some of them can be already existing, so I need to get this trigger working row by row. I found on the Internet that Oracle has something like FOR EACH ROW, but I didn't find the same for SQL Server 2000. Just D. Show quote "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message news:uPlKRzKwFHA.2072@TK2MSFTNGP14.phx.gbl... > Just, > > What you're refering to is Recursive Triggers which are disabled by > default. > > Is it possible for you to use a PK or UNIQUE constraint instead of a > trigger to maintain entity integrity? More than likely a better approach. > > HTH > > Jerry > "Just D." <no@spam.please> wrote in message > news:Zf2Ze.147058$Ji4.13923@fed1read03... >> Is it correct to write this way? Does it mean that the trigger will not >> call itself as a recurse? >> >> I wrote a INSTEAD OF INSERT trigger working with the same table where >> it's stored, the trigger checks if the record with same properties >> already exists in this table and just replaces INSERT with UPDATE against >> the same table if the record exists to avoid duplications. Does it mean >> that when the trigger initiates INSERT the same trigger INSTEAD OF INSERT >> will not be called? And if so how can we avoid this situation? >> >> Also how can we work with the "inserted" row by row? Do we have something >> like FOR EACH ROW in SQL Server 2000 to use it in the triggers? >> >> Just D. >> >> > > Check out the Multirow Considerations topic in the SQL Server Books Online.
HTH Jerry "Just D." <no@spam.please> wrote in message news:YH2Ze.147060$Ji4.127553@fed1read03...Show quote > Hi Jerry, > > Yes, I'm actually using the PK inside this trigger to understand if the > inserted record already exists to replace INSERT by UPDATE with the same > fields, but I can't understand if it's applied to the whole INSERTED as a > table or works row by row? I'd prefer a second way but I don't know how to > do that, because in most cases many rows should be inserted and some of > them can be already existing, so I need to get this trigger working row by > row. I found on the Internet that Oracle has something like FOR EACH ROW, > but I didn't find the same for SQL Server 2000. > > Just D. > > "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message > news:uPlKRzKwFHA.2072@TK2MSFTNGP14.phx.gbl... >> Just, >> >> What you're refering to is Recursive Triggers which are disabled by >> default. >> >> Is it possible for you to use a PK or UNIQUE constraint instead of a >> trigger to maintain entity integrity? More than likely a better >> approach. >> >> HTH >> >> Jerry >> "Just D." <no@spam.please> wrote in message >> news:Zf2Ze.147058$Ji4.13923@fed1read03... >>> Is it correct to write this way? Does it mean that the trigger will not >>> call itself as a recurse? >>> >>> I wrote a INSTEAD OF INSERT trigger working with the same table where >>> it's stored, the trigger checks if the record with same properties >>> already exists in this table and just replaces INSERT with UPDATE >>> against the same table if the record exists to avoid duplications. Does >>> it mean that when the trigger initiates INSERT the same trigger INSTEAD >>> OF INSERT will not be called? And if so how can we avoid this situation? >>> >>> Also how can we work with the "inserted" row by row? Do we have >>> something like FOR EACH ROW in SQL Server 2000 to use it in the >>> triggers? >>> >>> Just D. >>> >>> >> >> > > Good advice, thanks. That's what I found. Not so much.
http://msdn2.microsoft.com/en-us/library/ms250511 Here is another article. Sometimes people do a crazy stuff to get a positive result. http://www.codeproject.com/database/columnsrecomputing.asp Just D. Show quote "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message news:OCof5yLwFHA.664@tk2msftngp13.phx.gbl... > Check out the Multirow Considerations topic in the SQL Server Books > Online. > > HTH > > Jerry > "Just D." <no@spam.please> wrote in message > news:YH2Ze.147060$Ji4.127553@fed1read03... >> Hi Jerry, >> >> Yes, I'm actually using the PK inside this trigger to understand if the >> inserted record already exists to replace INSERT by UPDATE with the same >> fields, but I can't understand if it's applied to the whole INSERTED as a >> table or works row by row? I'd prefer a second way but I don't know how >> to do that, because in most cases many rows should be inserted and some >> of them can be already existing, so I need to get this trigger working >> row by row. I found on the Internet that Oracle has something like FOR >> EACH ROW, but I didn't find the same for SQL Server 2000. >> >> Just D. >> >> "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message >> news:uPlKRzKwFHA.2072@TK2MSFTNGP14.phx.gbl... >>> Just, >>> >>> What you're refering to is Recursive Triggers which are disabled by >>> default. >>> >>> Is it possible for you to use a PK or UNIQUE constraint instead of a >>> trigger to maintain entity integrity? More than likely a better >>> approach. >>> >>> HTH >>> >>> Jerry >>> "Just D." <no@spam.please> wrote in message >>> news:Zf2Ze.147058$Ji4.13923@fed1read03... >>>> Is it correct to write this way? Does it mean that the trigger will not >>>> call itself as a recurse? >>>> >>>> I wrote a INSTEAD OF INSERT trigger working with the same table where >>>> it's stored, the trigger checks if the record with same properties >>>> already exists in this table and just replaces INSERT with UPDATE >>>> against the same table if the record exists to avoid duplications. Does >>>> it mean that when the trigger initiates INSERT the same trigger INSTEAD >>>> OF INSERT will not be called? And if so how can we avoid this >>>> situation? >>>> >>>> Also how can we work with the "inserted" row by row? Do we have >>>> something like FOR EACH ROW in SQL Server 2000 to use it in the >>>> triggers? >>>> >>>> Just D. >>>> >>>> >>> >>> >> >> > > Look up UPSERT and/or MERGE in other SQL products.
Put this into a stored procedure. And please, please, please, please learn that rowes are not records. I wouldn't like to be very annoying but please don't hesitate, just take a
look: http://search.microsoft.com/search/results.aspx?qu=sql+record&View=msdn&st=b&c=0&s=1&swc=0 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg04_3s8k.asp <-----> Record Object A Record object represents one row of data, and has some conceptual similarities with a one-row Recordset. An application can retrieve Record objects directly from the provider instead of a one-row Recordset, for example when an SQL query that selects only one row (singleton select) is executed. It is much more efficient for an application to use a Record object than a Recordset object if only one row is to be retrieved from a query. A Record object also can be obtained directly from a Recordset object. <-----> Anyway you're the only one who doesn't like the messages that people write here in the newsgroup. If' I'm talking about records that means records. All people around understand that but you. Thanks, Just D. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1127532873.374296.253240@o13g2000cwo.googlegroups.com... > Look up UPSERT and/or MERGE in other SQL products. > > Put this into a stored procedure. And please, please, please, please > learn that rowes are not records. I think we're about to see an example of an "infinite newsgroup loop". ;-)
"Just D." <no@spam.please> wrote in message news:Dv4Ze.147775$Ji4.56124@fed1read03...Show quote >I wouldn't like to be very annoying but please don't hesitate, just take a >look: > > http://search.microsoft.com/search/results.aspx?qu=sql+record&View=msdn&st=b&c=0&s=1&swc=0 > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg04_3s8k.asp > > <-----> > Record Object > > A Record object represents one row of data, and has some conceptual > similarities with a one-row Recordset. An application can retrieve Record > objects directly from the provider instead of a one-row Recordset, for > example when an SQL query that selects only one row (singleton select) is > executed. It is much more efficient for an application to use a Record > object than a Recordset object if only one row is to be retrieved from a > query. > A Record object also can be obtained directly from a Recordset object. > <-----> > > Anyway you're the only one who doesn't like the messages that people write > here in the newsgroup. > > If' I'm talking about records that means records. All people around > understand that but you. > > Thanks, > Just D. > > "--CELKO--" <jcelko***@earthlink.net> wrote in message > news:1127532873.374296.253240@o13g2000cwo.googlegroups.com... >> Look up UPSERT and/or MERGE in other SQL products. >> >> Put this into a stored procedure. And please, please, please, please >> learn that rowes are not records. > > No, I'm not going to discuss the obvious things again and again, I spent too
many years with computers, 26 or longer, about 15-20 years on the Fidonet and Internet, to really understand what's flame and how it affects a newsgroup. :) I just posted one answer when I noticed that this guy likes to discuss and teach instead of a simple answering all people around. Maybe he's right, but the time goes ahead changing things and definitions. Finally it's a part of terminology itself, BG changed a lot in the computer terminology as well. It's nothing to the code. Thanks, Just D. Show quote "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message news:%23wGM$$LwFHA.596@TK2MSFTNGP12.phx.gbl... >I think we're about to see an example of an "infinite newsgroup loop". ;-) > > "Just D." <no@spam.please> wrote in message > news:Dv4Ze.147775$Ji4.56124@fed1read03... >>I wouldn't like to be very annoying but please don't hesitate, just take a >>look: >> >> http://search.microsoft.com/search/results.aspx?qu=sql+record&View=msdn&st=b&c=0&s=1&swc=0 >> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg04_3s8k.asp >> >> <-----> >> Record Object >> >> A Record object represents one row of data, and has some conceptual >> similarities with a one-row Recordset. An application can retrieve Record >> objects directly from the provider instead of a one-row Recordset, for >> example when an SQL query that selects only one row (singleton select) is >> executed. It is much more efficient for an application to use a Record >> object than a Recordset object if only one row is to be retrieved from a >> query. >> A Record object also can be obtained directly from a Recordset object. >> <-----> >> >> Anyway you're the only one who doesn't like the messages that people >> write here in the newsgroup. >> >> If' I'm talking about records that means records. All people around >> understand that but you. >> >> Thanks, >> Just D. >> >> "--CELKO--" <jcelko***@earthlink.net> wrote in message >> news:1127532873.374296.253240@o13g2000cwo.googlegroups.com... >>> Look up UPSERT and/or MERGE in other SQL products. >>> >>> Put this into a stored procedure. And please, please, please, please >>> learn that rowes are not records. >> >> > > Just D.
I believe you are referring to the "Record" object in ADO which represents 1 "row" of data. Celko is correct in that "that rowes are not records" in SQL. He just wants you to understand the difference. In the end, we all understand what you are talking about. Yosh "Just D." <no@spam.please> wrote in message news:Dv4Ze.147775$Ji4.56124@fed1read03...Show quote >I wouldn't like to be very annoying but please don't hesitate, just take a >look: > > http://search.microsoft.com/search/results.aspx?qu=sql+record&View=msdn&st=b&c=0&s=1&swc=0 > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg04_3s8k.asp > > <-----> > Record Object > > A Record object represents one row of data, and has some conceptual > similarities with a one-row Recordset. An application can retrieve Record > objects directly from the provider instead of a one-row Recordset, for > example when an SQL query that selects only one row (singleton select) is > executed. It is much more efficient for an application to use a Record > object than a Recordset object if only one row is to be retrieved from a > query. > A Record object also can be obtained directly from a Recordset object. > <-----> > > Anyway you're the only one who doesn't like the messages that people write > here in the newsgroup. > > If' I'm talking about records that means records. All people around > understand that but you. > > Thanks, > Just D. > > "--CELKO--" <jcelko***@earthlink.net> wrote in message > news:1127532873.374296.253240@o13g2000cwo.googlegroups.com... >> Look up UPSERT and/or MERGE in other SQL products. >> >> Put this into a stored procedure. And please, please, please, please >> learn that rowes are not records. > > An INSERT within an INSTEAD OF INSERT trigger will never cause the same
INSTEAD OF INSERT trigger to fire. It doesn't matter whether recursive triggers are enabled or not. The inserted pseudotable in the INSTEAD OF INSERT trigger contains all of the rows to be inserted. You can open a cursor on the inserted pseudotable to process each row individually; however, I question the necessity of that approach. Set based inserts are preferred because triggers only fire once, there is less activity in the transaction log, and updates to indexes can be optimized. An INSERT within an INSTEAD OF INSERT trigger will not cause the INSTEAD OF INSERT trigger to fire recursively, but any AFTER INSERT (or FOR INSERT) triggers will fire. "Just D." <no@spam.please> wrote in message news:Zf2Ze.147058$Ji4.13923@fed1read03...Show quote > Is it correct to write this way? Does it mean that the trigger will not > call itself as a recurse? > > I wrote a INSTEAD OF INSERT trigger working with the same table where it's > stored, the trigger checks if the record with same properties already > exists in this table and just replaces INSERT with UPDATE against the same > table if the record exists to avoid duplications. Does it mean that when > the trigger initiates INSERT the same trigger INSTEAD OF INSERT will not > be called? And if so how can we avoid this situation? > > Also how can we work with the "inserted" row by row? Do we have something > like FOR EACH ROW in SQL Server 2000 to use it in the triggers? > > Just D. > >  |
|||||||||||||||||||||||
Other interesting topics