Solved

trigger Before update copy row data to archive table

Posted on 2011-09-08
7
817 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

14 Experts available now in Live!

Get 1:1 Help Now