CODEWITHSUNDEEP
sql-servertriggers
gordon613
gordon613

Reputation: 2952

Using Triggers in SQL Server to keep a history

I am using SQL Server 2012

I have a table called AMOUNTS and a table called AMOUNTS_HIST

Both tables have identical columns:

  1. CHANGE_DATE
  2. AMOUNT
  3. COMPANY_ID
  4. EXP_ID
  5. SPOT
  6. UPDATE_DATE [system date]

The Primary Key of AMOUNTS is COMPANY_ID and EXP_ID. The Primary Key pf AMOUNTS_HIST is COMPANY_ID, EXP_ID and CHANGE_DATE

Whenever I add a row in the AMOUNTS table, I would like to create a copy of it in the AMOUNTS_HIST table. [Theoretically, each time a row is added to 'AMOUNTS', the COMPANY_ID, EXP_ID, CHANGE_DATE will be unique. Practically, if they are not, the relevant row in AMOUNTS_HIST would need to be overridden. The code below does not take the overriding into account.]

I created a trigger as follows: 

CREATE TRIGGER [MYDB].[update_history] ON [MYDB].[AMOUNTS]
FOR UPDATE
AS
INSERT MYDB.AMOUNTS_HIST (
    CHANGE_DATE,
    COMPANY_ID,
    EXP_ID,
    SPOT
    UPDATE_DATE
    )
SELECT e.CHANGE_DATE,
    e.COMPANY_ID,
    e.EXP_ID
    e.REMARKS,
    e.SPOT,
    e.UPDATE_DATE
FROM MYDB.AMOUNTS e
JOIN inserted ON inserted.company_id = e.company_id
    AND inserted.exp_id=e.exp_id

I don't understand why it does nothing at all in my AMOUNTS_HIST table. Can anyone help?

Thanks,

Upvotes: 0

Views: 8224

Answers (2)

CandiedCode
CandiedCode

Reputation: 604

I just wanted to chime in. Have you looked at CDC (change data capture).

http://msdn.microsoft.com/en-us/library/bb522489(v=sql.105).aspx "Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.

Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server."

As far as your trigger goes, when you update [MYDB].[AMOUNTS] does the trigger throw any errors?

Also I believe you can get all your data from Inserted table without needed to do the join back to mydb.amounts.

Upvotes: 1

Randy Minder
Randy Minder

Reputation: 48522

Probably because the trigger, the way it's currently written, will only get fired when an Update is done, not an insert.

Try changing it to:

CREATE TRIGGER [MYDB].[update_history] ON [MYDB].[AMOUNTS]
FOR UPDATE, INSERT

Upvotes: 2

Related Questions