Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

rigger does not appear to work on multi-row updates

Posted on 2012-03-16
9
Medium Priority
?
623 Views
Last Modified: 2012-03-25
memberEarnings-Update.txtI have several triggers (see attachment for example) that insert a row into a change log table when updates are made to the triggered table. This seems to work quite well for single row updates, e.g. when a use changes a column value via a screen form. However, if a program updates several rows at once, either via a single query that updates multiple rows, or via individual queries executed in a programatic 'for' loop, it seems that only one row gets inserted into the log table, not one for each update.

What's up with this? Do I have to put some kind of seeing on the table or database or is this not doable?
0
Comment
Question by:jmarkfoley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 37729752
You must change your triggers to deal with batch operations and you should use INSERTED and DELETED tables for this matter than for multiple rows in any of these two internal tables write a batch operation - I recommend do NOT use cursors in triggers as they can have pretty big negative performance hit.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 37729771
In the trigger you can use following code to see if there are multiple or single rows affected:

IF (SELECT COUNT(*) FROM INSERTED) > 1
--then you must write trigger action for batch operation
ELSE
--single row affected


Same thing for DELETED table on DELETE trigger action.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 37731332
(Not for points --) In case it's not clear, there will be a temporary table named "INSERTED" when an INSERT trigger fires. The trigger can reference the rows in the table named INSERTED if the trigger needs to know that multiple rows were inserted. There will be one row in the INSERTED table for each row that was inserted. The format of the INSERTED table will always match whatever table the trigger is defined over.

For a DELETE trigger, there will be a temporary table named DELETED. It can be used pretty much the same way an INSERTED table would be used.

Basically, the trigger would put a row into the change log for every row in the INSERTED (or the DELETED) table.

Tom
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:jmarkfoley
ID: 37732952
Here's a fragment of the code I have now:

if UPDATE(earningCode) begin
    select @memberId = memberId from DELETED
    select @oldVal = case when earningCode is null then '' else earningCode end from DELETED
    select @recId = recId from DELETED
    select @newVal = case when earningCode is null then '' else earningCode end from INSERTED

  if @oldVal <> @newVal
  begin
    INSERT INTO changeLog (tableName,columnName,userName,changeTime,memberId,keyField,oldValue,newValue)
      values ('memberEarnings','earningCode',@uname,getdate(),@memberId,@recId,@oldVal,@newVal)
  end
end

Open in new window


This code assumes 1 change, as you all have mentioned. How would I transform this to loop through updates? What's the syntax for looping through updates, FETCH NEXT FROM INSERTED ... ? How would I retrieve the corresponding DELETED row?
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 1000 total points
ID: 37738105
Something liek code below but please double chek the code and test first!
Create Trigger  [test].[table_upd]
 On [test].[table]
 After UPDATE
 As
Begin

declare @memberid int
declare @oldval int
declare @newval int
declare @recid int
declare @uname sysname

if UPDATE(earningCode) AND (select count(*) from inserted) = 1
begin -- single insert

    select @memberId = memberId from DELETED
    select @oldVal = case when earningCode is null then '' else earningCode end from DELETED
    select @recId = recId from DELETED
    select @newVal = case when earningCode is null then '' else earningCode end from INSERTED

  if @oldVal <> @newVal
  begin
    INSERT INTO changeLog (tableName,columnName,userName,changeTime,memberId,keyField,oldValue,newValue)
      values ('memberEarnings','earningCode',@uname,getdate(),@memberId,@recId,@oldVal,@newVal)
  end
end --single insert


if UPDATE(earningCode) AND (select count(*) from inserted) > 1
declare @loop_table table (recId int)
insert into @loop_table select recId from inserted union select recId from deleted

while (select count(*) from @loop_table) > 0
begin -- loop
    set @recId = (select top 1 id from @loop_table order by id)

    select @memberId = memberId from DELETED where recid = @recId
    select @oldVal = case when earningCode is null then '' else earningCode end from DELETED where recid = @recId
    select @newVal = case when earningCode is null then '' else earningCode end from INSERTED where recid = @recId
    select @recId = recId from DELETED where recid = @recId
      if @oldVal <> @newVal
      begin
            INSERT INTO changeLog (tableName,columnName,userName,changeTime,memberId,keyField,oldValue,newValue)
            values ('memberEarnings','earningCode',@uname,getdate(),@memberId,@recId,@oldVal,@newVal)
      end

end -- loop
end
GO
0
 
LVL 40

Expert Comment

by:lcohan
ID: 37738113
OOOPs..Missed the delete so it would be a endless loop - please add followinfg line just before the end -- loop:

   delete from @loop_table where recid = @recid -- this is to removed processed row and skip to next
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 37739532
It's fastest to use set-based statements in SQL Server, like below.

I wasn't 100% of the values to use in the COALESCEs in the WHERE clause, so you may have to adjust those.



USE [HPRS]
GO
/****** Object:  Trigger [dbo].[memberEarnings_Update]    Script Date: 03/16/2012 10:48:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* trigger: memberEarnings_Update
    author: Mark Foley - Novatec Software Engineering, LLC - mfoley@novatec-inc.com
      date: 05-DEC-2011

 This trigger logs changes made to the memberEarnings table.
*/

ALTER TRIGGER [dbo].[memberEarnings_Update]
ON [dbo].[memberEarnings]
AFTER UPDATE
AS

declare @uname varchar(30)
declare @ix integer

SET NOCOUNT ON

set @uname = suser_sname()
set @ix = charindex('\',@uname)
set @uname = substring(@uname,@ix + 1, len(@uname) - @ix)
if @uname = 'webuser' set @uname = host_name()

-- will only insert for a change to one of these columns:
--   'earningCode', 'amount', 'enabled', and 'oneShot'.
-- every column change is inserted separately:
INSERT INTO dbo.changeLog (tableName,columnName,userName,changeTime,memberId,keyField,oldValue,newValue)
SELECT
    'memberEarnings', column_name, @uname, getdate(), i.memberId,
    CASE column_name
        WHEN 'earningCode' THEN CAST(d.keyId AS varchar(11))
        ELSE earningCode END AS keyField,    
    CASE column_name
        WHEN 'earningCode' THEN CAST(d.earningCode AS varchar(80))
        WHEN 'amount'      THEN CAST(d.amount      AS varchar(80))
        WHEN 'enabled'     THEN CAST(d.enabled     AS varchar(80))
        WHEN 'oneShot'     THEN CAST(d.oneShot     AS varchar(80))
    END AS oldValue,
    CASE column_name
        WHEN 'earningCode' THEN CAST(i.earningCode AS varchar(80))
        WHEN 'amount'      THEN CAST(i.amount      AS varchar(80))
        WHEN 'enabled'     THEN CAST(i.enabled     AS varchar(80))
        WHEN 'oneShot'     THEN CAST(i.oneShot     AS varchar(80))
    END AS newValue
FROM inserted i
INNER JOIN deleted d ON
    d.recId = i.recId
CROSS JOIN (
    SELECT 'earningCode' AS column_name WHERE UPDATE(earningCode) UNION ALL
    SELECT 'amount' WHERE UPDATE(amount) UNION ALL
    SELECT 'enabled' WHERE UPDATE(enabled) UNION ALL
    SELECT 'oneShot' WHERE UPDATE(oneShot)
) AS column_names
WHERE
    (column_name = 'earningCode' AND COALESCE(d.earningCode, '') <> COALESCE(i.earningCode, '')) OR
    (column_name = 'amount'      AND COALESCE(d.amount     , 0)  <> COALESCE(i.amount, 0))       OR
    (column_name = 'enabled'     AND COALESCE(d.enabled    , 0)  <> COALESCE(i.enabled, 0))      OR
    (column_name = 'oneShot'     AND COALESCE(d.oneShot    , '') <> COALESCE(i.oneShot, ''))      
   
GO
0
 
LVL 1

Author Comment

by:jmarkfoley
ID: 37763677
Hmmm, these already messy solutions can get even messier with tables having more columns. I think I'll stick with the single row update in the trigger. If I want to log multiple updates, I'll change my program to loop through the table records.
0
 
LVL 1

Author Closing Comment

by:jmarkfoley
ID: 37763681
Thanks!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question