Solved

trigger Before update copy row data to archive table

Posted on 2011-09-08
7
823 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

810 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