Solved

trigger Before update copy row data to archive table

Posted on 2011-09-08
7
828 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

820 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