Update trigger is value changes

Question

In a sql server 2008 r2 database, I am working with triggers for the first time. In an update trigger, I want to add a row to an audit table if the value of start_date changes in a table called 'main'.

Thus can you show me the sql on how to accomplish this goal?

Answer 1

create trigger trMainChange On Main AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(start_date) -- the update has touched this column - not necessary changed
   insert into Audit (...)
   select ... from Inserted

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

I changed my trigger to look like the following but it is not working. There are no errors, but the audit table is updated sporadically.

I basically want a row in the audit table when the start date changes.

USE [DEV]
GO
SET  ANSI_NULLS ON
GO
SET  QUOTED_IDENTIFIER ON
GO

CREATE  TRIGGER [dbo].[update_trigger]ON [dbo].[Transaction_Tracking]
AFTER  UPDATE
AS
BEGIN
INSERT
 INTO [dbo].[Transaction_Audit]
( Package_ID, Received_Date, Start_Date,Operation, TriggerTable)
SELECT  i.Package_ID, i.Received_Date, i.Start_Date,'U', 'I'
FROM  inserted i
INNER  JOIN deleted d ON i.Track_ID=d.Track_ID
WHERE (SUBSTRING(i.Package_ID,1,3) = 'RVW' or SUBSTRING(d.Package_ID,1,3) = 'RVW')
and  i.Start_Date<>d.Start_Date
;
END
;
GO

Answer 3

The normal way you do INSERT INTO. The Audit table, I suppose, repeats most of your main table columns (may have few extra). So, the code is

insert into Audit (column1, column2, ...)

select Column1, Column2, some extra calculated fields if needed such as user name and time 

from Inserted

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

If start_date is NULL when you update it, your where clause will fail to include the row(s). 

Answer 5

As Scott noted, your code will not work in case your date is NULL. You may try 

AND COALESCE(i.StartDate, '19000101') <> COALESCE(d.Start_Date, '19000101') assuming you never use that date normally. Or you may pick up

'30000101' instead.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

Have you looked at audit tools? Microsoft even has one!

I do not feel competent to handle Sarbanes-Oxley, FDA Part 11, PCI-CISP, HIPAA requirement and all the other things that can send you to jail.

I much prefer to get a tool from someone who has lawyers to protect me. Google around for tools, but this will get you started:

http://krell-software.com/omniaudit/

http://www.apexsql.com/promo/sql_data_auditing_tool.aspx?_kk=sql%20server%20audit&_kt=6bc4e815-b5a5-4202-809a-280ce0a3e099&gclid=CPPV453OpbUCFdSLPAod5g8Agg

http://www.isaca.org/Knowledge-Center/Research/ResearchDeliverables/Pages/Microsoft-SQL-Server-Database-Audit-Assurance-Program.aspx

Reinventing the wheel at great cost, but it can have some legal problems.  

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL