|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Triggers - run for multiple updatedI am new to trigger and I have following question.
I have two tables: "Customer" & "AccountDetails". "Customer" stored customer's personal information, so one customer will be at one row. "AccountDetails" stored all the accounts information which tie to each customer, so there will be multiple rows for a customer since one customer can have mulitple accounts. Right now I have a updated trigger on "AccountDetails" table. When there is an updated to this table, it will insert some data to another "CustLog" table for logging when customer does the updates. Let's say Customer "A" has 5 accounts. When "A" updates his accounts' information, all 5 rows will be updated, triggers will be called 5 times, and 5 insert rows will be added to "CustLog". Is that any way to keep track those 5 updated on "AccountDetails" are referred to the same customer (by customer ID or so) so that it will only run the trigger once instead of 5 times? I hope I make it clear and please help me on this! Hello Vanessa,
You can do bluck operation through stored procedures not through triggers. Trigger will fire for a record. Triggers are designed for this reason. Upto my knowledge triggers are not helpful for your bluk operations. With Regards, AHARI Software Engineer. Show quote "Vanessa" wrote: > I am new to trigger and I have following question. > > I have two tables: "Customer" & "AccountDetails". "Customer" stored > customer's personal information, so one customer will be at one row. > "AccountDetails" stored all the accounts information which tie to each > customer, so there will be multiple rows for a customer since one customer > can have mulitple accounts. > > Right now I have a updated trigger on "AccountDetails" table. When there is > an updated to this table, it will insert some data to another "CustLog" table > for logging when customer does the updates. Let's say Customer "A" has 5 > accounts. When "A" updates his accounts' information, all 5 rows will be > updated, triggers will be called 5 times, and 5 insert rows will be added to > "CustLog". Is that any way to keep track those 5 updated on "AccountDetails" > are referred to the same customer (by customer ID or so) so that it will only > run the trigger once instead of 5 times? > > I hope I make it clear and please help me on this! > You can do bluck operation through stored procedures not through triggers. That's not correct. Triggers fire once per update NOT once per row. You > Trigger will fire for a record. Triggers are designed for this reason. > Upto my knowledge triggers are not helpful for your bluk operations. should write your triggers to perform correctly no matter how many rows are updated. That's why the INSERTED and DELETED virtual tables are useful. -- David Portas SQL Server MVP -- Hi Vanessa
A trigger is called per statement. This can change multiple rows, therefore you will need to code your trigger to expect multiple rows in the inserted and deleted tables. If you wish to post your procedure, trigger and table definitions (DDL) along with sample data you may get a more precise answer see http://www.aspfaq.com/etiquette.asp?id=5006 for more information on how to do this. John Show quote "Vanessa" <Vane***@discussions.microsoft.com> wrote in message news:E993C740-D0F7-4DB2-A18E-480711501381@microsoft.com... >I am new to trigger and I have following question. > > I have two tables: "Customer" & "AccountDetails". "Customer" stored > customer's personal information, so one customer will be at one row. > "AccountDetails" stored all the accounts information which tie to each > customer, so there will be multiple rows for a customer since one customer > can have mulitple accounts. > > Right now I have a updated trigger on "AccountDetails" table. When there > is > an updated to this table, it will insert some data to another "CustLog" > table > for logging when customer does the updates. Let's say Customer "A" has 5 > accounts. When "A" updates his accounts' information, all 5 rows will be > updated, triggers will be called 5 times, and 5 insert rows will be added > to > "CustLog". Is that any way to keep track those 5 updated on > "AccountDetails" > are referred to the same customer (by customer ID or so) so that it will > only > run the trigger once instead of 5 times? > > I hope I make it clear and please help me on this! So let's say a single update or delete SQL statement, which will affect 10
rows in a table, is executed, the trigger is only called once instead of 10 because trigger is called per statement, is it correct? Actually our customers do their accounts updating through another application, for which is developed by outside contractors and I have no access to it. But then my boss wants to keep track when customers use that application to update their accounts (accounts with us but not those bank accounts $$ :P) meanwhile he doesn't want to alter our current application by paying more the contractors :( Therefore, I have written up a trigger at that AccountDetails table. So whenever there is an update, it will call the trigger and save certainly data to another log table for logging the updating. I will try to post up my trigger and DDL as soon as I get back to work tomorrow! Thus, if my concept in above is correct, which means if a customer updates his or her accounts, that application actually does 5 updates statement instead of 1 so it called my triggers 5 times! If that the case, can I still code my trigger to expect multiple rows in the inserted and deleted tables? Thanks alot!!!! Vanessa - a beginner in trigger :( Show quote "John Bell" wrote: > Hi Vanessa > > A trigger is called per statement. This can change multiple rows, therefore > you will need to code your trigger to expect multiple rows in the inserted > and deleted tables. > > If you wish to post your procedure, trigger and table definitions (DDL) > along with sample data you may get a more precise answer see > http://www.aspfaq.com/etiquette.asp?id=5006 for more information on how to > do this. > > John > > "Vanessa" <Vane***@discussions.microsoft.com> wrote in message > news:E993C740-D0F7-4DB2-A18E-480711501381@microsoft.com... > >I am new to trigger and I have following question. > > > > I have two tables: "Customer" & "AccountDetails". "Customer" stored > > customer's personal information, so one customer will be at one row. > > "AccountDetails" stored all the accounts information which tie to each > > customer, so there will be multiple rows for a customer since one customer > > can have mulitple accounts. > > > > Right now I have a updated trigger on "AccountDetails" table. When there > > is > > an updated to this table, it will insert some data to another "CustLog" > > table > > for logging when customer does the updates. Let's say Customer "A" has 5 > > accounts. When "A" updates his accounts' information, all 5 rows will be > > updated, triggers will be called 5 times, and 5 insert rows will be added > > to > > "CustLog". Is that any way to keep track those 5 updated on > > "AccountDetails" > > are referred to the same customer (by customer ID or so) so that it will > > only > > run the trigger once instead of 5 times? > > > > I hope I make it clear and please help me on this! > > > If the application does 5 updates then the trigger will fire 5 times
and you won't see that as a single update. If the application does a single update of 5 rows then the trigger fires once only. Either way you should write your trigger to perform correctly no matter how many rows are updated: 0, 1 or multiple rows. -- David Portas SQL Server MVP -- Thanks David,
Then I think our application does 5 updates since the trigger is called 5 times! Can you show me how to have the trigger to get multiple rows updated? Here is my trigger: CREATE TRIGGER cancelSO ON [dbo].[AccountDetails] FOR UPDATE AS IF EXISTS (SELECT * FROM inserted WHERE ORDER_QTY=CAN_QTY) BEGIN DECLARE @SO varchar(30) DECLARE @CustPO varchar(30) SET @SO = (SELECT ORD_NUM FROM inserted) IF EXISTS (SELECT * FROM ORDERS WHERE ORD_NUM=@SO) BEGIN SET @CustPO = (SELECT PO_NUM FROM ORDERS WHERE ORD_NUM=@SO) INSERT CustLog (OrderType, ActionType, SO, CustPO, CustID, KeyDate, SentFlag) SELECT 'SO', 'cancel', ORD_NUM, @CustPO, CUS_ID, GETDATE(), 'N' FROM inserted END END Show quote "David Portas" wrote: > If the application does 5 updates then the trigger will fire 5 times > and you won't see that as a single update. If the application does a > single update of 5 rows then the trigger fires once only. Either way > you should write your trigger to perform correctly no matter how many > rows are updated: 0, 1 or multiple rows. > > -- > David Portas > SQL Server MVP > -- > > On Mon, 26 Sep 2005 10:01:02 -0700, Vanessa wrote:
Show quote >Thanks David, Hi Vanessa,> >Then I think our application does 5 updates since the trigger is called 5 >times! Can you show me how to have the trigger to get multiple rows updated? >Here is my trigger: > >CREATE TRIGGER cancelSO ON [dbo].[AccountDetails] >FOR UPDATE >AS >IF EXISTS (SELECT * FROM inserted WHERE ORDER_QTY=CAN_QTY) > BEGIN > DECLARE @SO varchar(30) > DECLARE @CustPO varchar(30) > SET @SO = (SELECT ORD_NUM FROM inserted) > IF EXISTS (SELECT * FROM ORDERS WHERE ORD_NUM=@SO) > BEGIN > SET @CustPO = (SELECT PO_NUM FROM ORDERS WHERE ORD_NUM=@SO) > INSERT CustLog (OrderType, ActionType, SO, CustPO, CustID, KeyDate, >SentFlag) > SELECT 'SO', 'cancel', ORD_NUM, @CustPO, CUS_ID, GETDATE(), 'N' > FROM inserted > END > END The following is untested. Please read www.aspfaq.com/5006 if you prefer tested replies. CREATE TRIGGER cancelSO ON dbo.AccountDetails FOR UPDATE AS INSERT CustLog (OrderType, ActionType, SO, CustPO, CustID, KeyDate, SentFlag) SELECT 'SO', 'cancel', i.ORD_NUM, o.CustPO, i.CUS_ID, GETDATE(), 'N' FROM inserted AS i INNER JOIN ORDERS AS o ON o.ORD_NUM = i.ORD_NUM WHERE i.ORDER_QTY = i.CAN_QTY go Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Hi,
I have had the similar problem nearly six months ago. I solved using "cursor" because my trigger makes complex operations so that other inserted records are lost. " declare cursor ASD select * from inserted " thats the way i get all of the updated records. In 20+ years of SQL coding, 10 years on the Standards Committe, and few
books on the language, I have written five cursors; if we had CASE expressions I know I could have avoided three of them. So I doubt this was a good solution. Triggers give the optimizer no help, do not port and are slow. The right way, if you can do it, is to use DRI actions and a normalized schema. Since Vanessa did not post any DDL, code or clear specs, it is hard to give her any help. But I doubt this was a good solution for her, too. How do you enforce transition constraints? Neither DRI actions nor
normalization provide a mechanism. Do you just ignore them? Do you enforce them in the application program? What about inclusion dependencies? Normalization and DRI are not sufficient for these. What about constraints that surround complex dependencies between rows within the same table--for example, preventing overlapping time intervals? Normalization and DRI don't work for these either. Constraints must be enforced by the RDBMS--not an application program. That's one of Codd's fundamental rules. Otherwise there's a back door available that does not enforce the constraints and consequently allows garbage to be stored. Triggers provide a means to enforce all of the constraints enumerated above. Cursors are a tool. I use them where necessary to work around the limitations of the database engine or SQL in general. The fact that you CAN usually avoid a cursor by writing a complex set-based query does not ALWAYS mean that you SHOULD. A majority of the cursors I've written started out as set-based operations that performed so abysmally that they were unusable. It is usually preferable to use set-based operations, since there are significant performance benefits for set-based updates over row-based updates, but there are times when a cursor will outperform a set-based operation--sometimes by several orders of magnitude. If you can eliminate one iteration step from an execution plan by using a cursor, then it is usually worthwhile (from a performance standpoint) to do so. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1127611610.506659.162700@g47g2000cwa.googlegroups.com... > In 20+ years of SQL coding, 10 years on the Standards Committe, and few > books on the language, I have written five cursors; if we had CASE > expressions I know I could have avoided three of them. So I doubt this > was a good solution. > > Triggers give the optimizer no help, do not port and are slow. The > right way, if you can do it, is to use DRI actions and a normalized > schema. Since Vanessa did not post any DDL, code or clear specs, it is > hard to give her any help. But I doubt this was a good solution for > her, too. > >> How do you enforce transition constraints .. inclusion dependencies .. dependencies between rows within the same table? << Self-referencing CHECK() constraints and CREATE ASSERTION statements.Unfortunately, SQL Server and other products do not yet have these SQL-92 features after all this time. So you "fake it" with triggers and perhaps stored procedures. >> there are times when a cursor will outperform a set-based operation--sometimes by several orders of magnitude. << Granted; and are times someone wins the Lottery. I would guess than90-95% of the time in practice, a cursor is needed because the "table" is not a table at all, but a sequential file written without a proper key or normalization. I would guess than 95-99% of the time in practice, a cursor is **used** because the SQL programmer is not an SQL programmer at all, but a procedural programmer writing 3GL in thin disguise. The one common opertion I can think of for a cursor beating out set-oriented code is computing a Median in SQL Server before the OLAP extensions from SQL-99 were added. This is not true in other SQL products, but thanks to the physical storage model used in SQL Server, it works better than self-joins. >> If you can eliminate one iteration step from an execution plan by using a cursor, then it is usually worthwhile (from a performance standpoint) to do so. << Unh? Cursors usually add a loop rather than take it away.> Unh? Cursors usually add a loop rather than take it away Not always so. There are times when multiple calculations can be performed within the fetch loop that in a set-based operation require separate correlated subqueries or separate self-joins. It is easy to see that by making a single linear pass through the data with a cursor instead of an exponential number of passes through the same data with self-joins, you can achieve a huge performance improvement with a cursor. Usually a self-join is required when a dependency of some kind exists between rows in the same table. This situation, while uncommon, does not violate general relational principles, nor is it limited to tables with surrogate keys or IDENTITY. In fact, it occurs more often in tables without surrogates, because you're more likely to have embedded functional or multi-valued dependencies within a database that has tables with multi-column primary keys--even if it's in 5NF. If you use surrogates, you can extract the columns participating in the embedded functional or multi-valued dependency into a separate table or group of tables and use declarative means to maintain integrity instead of expensive self-joins. Those of us in the real world can't just change database engines or wait until certain features are implemented. We have to work with the cards we've been dealt and make the best of it. Sometimes that means using cursors, triggers, and IDENTITY. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1127638074.511683.25790@f14g2000cwb.googlegroups.com... >>> How do you enforce transition constraints .. inclusion dependencies .. >>> dependencies between rows within the same table? << > > Self-referencing CHECK() constraints and CREATE ASSERTION statements. > Unfortunately, SQL Server and other products do not yet have these > SQL-92 features after all this time. So you "fake it" with triggers > and perhaps stored procedures. > >>> there are times when a cursor will outperform a set-based >>> operation--sometimes by several orders of magnitude. << > > Granted; and are times someone wins the Lottery. I would guess than > 90-95% of the time in practice, a cursor is needed because the "table" > is not a table at all, but a sequential file written without a proper > key or normalization. I would guess than 95-99% of the time in > practice, a cursor is **used** because the SQL programmer is not an SQL > programmer at all, but a procedural programmer writing 3GL in thin > disguise. > > The one common opertion I can think of for a cursor beating out > set-oriented code is computing a Median in SQL Server before the OLAP > extensions from SQL-99 were added. This is not true in other SQL > products, but thanks to the physical storage model used in SQL Server, > it works better than self-joins. > >>> If you can eliminate one iteration step from an execution plan by using >>> a cursor, then it is usually worthwhile (from a performance standpoint) >>> to do so. << > > Unh? Cursors usually add a loop rather than take it away. > >> Usually a self-join is required when a dependency of some kind exists between rows in the same table... It is easy to see that by making a single linear pass through the data with a cursor instead of an exponential number of passes through the same data with self-joins, you can achieve a huge performance improvement with a cursor. << That is implementation dependent. If I have an SQL engine that useshashing instead of indexing for access, then the rows that need to be self-joined will commonly appear in the same hash bucket (Teradata). A surrogate key will not do that, and a cursor would prevent parallelism. In other products, I can set up "pre-joined" indexes. >> Those of us in the real world can't just change database engines or wait until certain features are implemented. We have to work with the cards we've been dealt and make the best of it. << I write a Standard SQL solution first, then comment it out and dropinto local dialect, if I have to resort to proprietary code or kludges to get it to work. Since 80% of the total cost of a system is in maintiaing it and not in coding it, the poor bastards that come after me have a Standard SQL solution that they can read without knowing dialect and which they can un-comment when the product catches up. People maintaining my code were so happy when *= was replaced with LEFT OUTER JOIN and it took them less than 5 minutes to upgrade. > I write a Standard SQL solution first, then comment it out and drop That's a very good practice. The only problem with it is that whenever a > into local dialect, if I have to resort to proprietary code or kludges > to get it to work. Since 80% of the total cost of a system is in > maintiaing it and not in coding it, the poor bastards that come after > me have a Standard SQL solution that they can read without knowing > dialect and which they can un-comment when the product catches up. > People maintaining my code were so happy when *= was replaced with LEFT > OUTER JOIN and it took them less than 5 minutes to upgrade. > change is made, the comment must be maintained as well, and unfortunately, programmers often fail to do it because they're either lazy, under strict time constraints or prevented by management in order to minimize cost. PHB's rarely take the long view. I therefore always read comments with a grain of salt, ever since I spent several days trying to find a bug in assembler code and discovered to my chagrin that the comments describing the routines hadn't been updated the last time a change was made. What we really need is a tool that translates Standard SQL into a particular dialect, using metadata or some other mechanism to control the resulting code. That way changes can be made to the Standard SQL, and any kludges, workarounds, or proprietary code would automatically be documented as such. That might also make it easier to port code to another database engine. Of course, the programmer or DBA would have to actually use the tool.... Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1127655829.999054.262880@f14g2000cwb.googlegroups.com... >>> Usually a self-join is required when a dependency of some kind exists >>> between rows in the same table... It is easy to see that by making a >>> single linear pass through the data with a cursor instead of an >>> exponential number of passes through the same data with self-joins, you >>> can achieve a huge performance improvement with a cursor. << > > That is implementation dependent. If I have an SQL engine that uses > hashing instead of indexing for access, then the rows that need to be > self-joined will commonly appear in the same hash bucket (Teradata). A > surrogate key will not do that, and a cursor would prevent parallelism. > In other products, I can set up "pre-joined" indexes. > >>> Those of us in the real world can't just change database engines or wait >>> until certain features are implemented. We have to work with the cards >>> we've been dealt and make the best of it. << > > I write a Standard SQL solution first, then comment it out and drop > into local dialect, if I have to resort to proprietary code or kludges > to get it to work. Since 80% of the total cost of a system is in > maintiaing it and not in coding it, the poor bastards that come after > me have a Standard SQL solution that they can read without knowing > dialect and which they can un-comment when the product catches up. > People maintaining my code were so happy when *= was replaced with LEFT > OUTER JOIN and it took them less than 5 minutes to upgrade. > >> What we really need is a tool that translates Standard SQL into a particular dialect, using metadata or some other mechanism to control theresulting code. << SwisSQL? There are some others. It may be the case that you needed a cursor to do this - but I
seriously doubt it. Cursors are not an optimal solution to most problems in SQL and I would recommend you don't use them at all in a trigger. Updates are set-based so triggers should be too. -- David Portas SQL Server MVP -- Hi David ,
my trigger consists on retrieving data from other tables do some operations and finish. lets say i have the following code for insert trigger declare @insID int slect @insID = id from inserted /* nearly hundred lines of code */ So , when a bulk update executes , the inserted rows unreachable unless using a cursor. I dont offer using cursors but i couldnot find other way. Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1127727509.718573.156280@g49g2000cwa.googlegroups.com... > It may be the case that you needed a cursor to do this - but I > seriously doubt it. Cursors are not an optimal solution to most > problems in SQL and I would recommend you don't use them at all in a > trigger. Updates are set-based so triggers should be too. > > -- > David Portas > SQL Server MVP > -- > > So , when a bulk update executes , the inserted rows unreachable unless That's not so. You just need to write set-based code that references> using a cursor. the DELETED and INSERTED tables directly rather than referencing a variable. Of course if you already have a quantity of legacy code that requires you to process one row at a time then you have to decide if it's worth rewriting that to cope with multiple rows - there may be some significant effort involved. My point was that if you are starting from scratch then it's usually simpler and more efficient to build set-based code to start with. -- David Portas SQL Server MVP -- Can you instruct me how to write set-based code that references
the DELETED and INSERTED tables directly rather than referencing a variable so that my trigger can cope with multiple rows updating. Thanks!!! Show quote "David Portas" wrote: > > So , when a bulk update executes , the inserted rows unreachable unless > > using a cursor. > > That's not so. You just need to write set-based code that references > the DELETED and INSERTED tables directly rather than referencing a > variable. Of course if you already have a quantity of legacy code that > requires you to process one row at a time then you have to decide if > it's worth rewriting that to cope with multiple rows - there may be > some significant effort involved. My point was that if you are starting > from scratch then it's usually simpler and more efficient to build > set-based code to start with. > > -- > David Portas > SQL Server MVP > -- > >  |
|||||||||||||||||||||||
Other interesting topics