?
Solved

trigger Before update copy row data to archive table

Posted on 2011-09-08
7
Medium Priority
?
842 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
[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
  • 4
  • 3
7 Comments
 
LVL 17

Accepted Solution

by:
dbaSQL earned 2000 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Copy Database Wizard 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.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

770 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