Solved

trigger Before update copy row data to archive table

Posted on 2011-09-08
7
814 Views
Last Modified: 2012-05-12
What I want to have happen  is  before an update or delete occurs   I want  the row being affected  to be copied out to an archive table. so I have a copy of what the data looked like before the update happened.

I'm not sure how to reference the data from the row being affected to insert it into an archive table of the same structure as the origingal.

ie tables
DEAL     and   DEAL_ARCHIVE

so and update happens to any column of deal  it copies  the entire unaltered  row affected over to DEAL_ARCHIVE then completes the update to DEAL.
0
Comment
Question by:unreal400
  • 4
  • 3
7 Comments
 
LVL 17

Accepted Solution

by:
dbaSQL earned 500 total points
ID: 36504704
0
 
LVL 2

Author Comment

by:unreal400
ID: 36505538
I've written a trigger for  update on a test product table.    The code works fine if i'm only altering one row  but if  do an     update where  ID IN (10,11,12,13)   so it is updating 4 rows with the value  It only
updates the last entry is there some looping I need to do?


 alter Trigger product_change_log
  On products
  INSTEAD of  UPDATE
  AS
  BEGIN
 
  DECLARE @PRODUCT_ID bigint,@PEOPLESOFT_CODE varchar(50),@START_DATE date,@STATUS varchar(10),@DESCRIPTION varchar(50),@DESCRIPTION_SHORT varchar(50)
 
  SELECT   @PRODUCT_ID = [PRODUCT_ID]
           ,@PEOPLESOFT_CODE = [PEOPLESOFT_CODE]
           ,@START_DATE = [START_DATE]
           ,@STATUS =[STATUS]
           ,@DESCRIPTION = [DESCRIPTION]
           ,@DESCRIPTION_SHORT = [DESCRIPTION_SHORT] FROM inserted
 
 
  Insert into RevisionHistory.dbo.PRODUCTS
  ([PRODUCT_ID]
           ,[PEOPLESOFT_CODE]
           ,[START_DATE]
           ,[STATUS]
           ,[DESCRIPTION]
           ,[DESCRIPTION_SHORT])
  SELECT
  [PRODUCT_ID]
           ,[PEOPLESOFT_CODE]
           ,[START_DATE]
           ,[STATUS]
           ,[DESCRIPTION]
           ,[DESCRIPTION_SHORT]
           from PRODUCTS where
           PRODUCT_ID = @PRODUCT_ID
           
UPDATE PRODUCTS  SET PEOPLESOFT_CODE = @PEOPLESOFT_CODE,START_DATE = @START_DATE, STATUS = @STATUS, DESCRIPTION = @DESCRIPTION , DESCRIPTION_SHORT = @DESCRIPTION_SHORT
where PRODUCT_ID = @PRODUCT_ID
                      
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36512417
Yes, yours is only going to impact the last record inserted.  No need to loop, a properly defined trigger can handle the mulitple row actions.  you need to reference inserted in your variable declaration AND your update statement.  

here's an example:

CREATE TRIGGER triggername ON tablename
FOR UPDATE AS  
UPDATE t
SET something = i.somethingelse
FROM tablename t INNER JOIN inserted i
  ON t.IDfield = i.IDfield
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 2

Author Comment

by:unreal400
ID: 36513053
Ok I have rewritten the trigger and it works beautifully for  tracking update commands.  But  here is the issue.  If a user right clicks a table and does "Edit top 200" through the grid    it will not commit the change saying to many rows have changed.    Ie  its seeing the extra insert and counting that as an issue why it cant update the single row.
Is there any way to compensate for this  in the trigger?

 alter Trigger product_change_log
  On products
  INSTEAD of  UPDATE
  AS
  BEGIN
 
  Insert into RevisionHistory.dbo.PRODUCTS
  ([PRODUCT_ID]
           ,[PEOPLESOFT_CODE]
           ,[START_DATE]
           ,[STATUS]
           ,[DESCRIPTION]
           ,[DESCRIPTION_SHORT]
           ,EVENT_TIMING)
  SELECT
                  p.[PRODUCT_ID]
           ,p.[PEOPLESOFT_CODE]
           ,p.[START_DATE]
           ,p.[STATUS]
           ,p.[DESCRIPTION]
           ,p.[DESCRIPTION_SHORT]
           ,GETDATE()
           from PRODUCTS p  
           RIGHT JOIN inserted i
           ON p.PRODUCT_ID = i.PRODUCT_ID
     
           
UPDATE PRODUCTS  SET PEOPLESOFT_CODE = i.PEOPLESOFT_CODE,START_DATE = i.START_DATE, STATUS = i.STATUS, DESCRIPTION = i.DESCRIPTION , DESCRIPTION_SHORT = i.DESCRIPTION_SHORT
FROM PRODUCTS p
RIGHT JOIN inserted i
on p.PRODUCT_ID = i.PRODUCT_ID

  END
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36513095
eeewgh.. gui updates.  I always try to prevent those types of manipulations.  At least on the big databases that I care about.  I can't say for sure, unreal400, but I think this is a limitation with gui actions of bulk like this, not the trigger itself.
0
 
LVL 2

Author Comment

by:unreal400
ID: 36513163
ya  that is a  bad GUI  for sure and I dont' like it either, was just trying to cover my bases to make sure it would cover me in all situations of users doing updates.

Thanks

0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36513179
no problem.  glad to have helped
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now