September 13, 2011 at 12:40 pm
Hi All,
In need of some help to create a trigger for a database when a record is changed in a table it fires and writes both the old and the new values to a history table along with a timestamp for record keeping purposes. Please help!!
A sample of the table that will change
tblEmployee
employeekey, firstName, LastName, ModifiedDate, ModifiedBy
tblEmployeeHistory
historyKey, ChangeDate, Attribute, OldValue, NewValue
So for instance when tbleEmployee changes the first name on record 1 from Joe to Joseph, but all other elements stay the same. I want to have a record written out to the tblEmployeeHistory with the values: key(identifier auto generated), daterecordchanged, "FirstName", "Joe", "Joseph"
Please help!
Thanks!
September 14, 2011 at 12:53 pm
Found this article, it helped me out tremendously.
September 15, 2011 at 8:14 am
Yes, that code works, but it does loop which is going to be a bit slow. I prefer to not write generic triggers like that because, while they are flexible, they tend to not perform as well as triggers specific for the table I'm working with. When I do auditing, I prefer to log the entire row and show what changed in a reporting procedure or SSRS report. I usually log the row from the deleted table because I can always use the current row to compare to the most recent log row. This means there is less logic applied in the trigger, which means it should perform faster, which is what I want since triggers are part of the original transaction.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 15, 2011 at 9:43 am
This is how I do it. See code below. I use the MiscValues table to set variables that I can use to turn things on/off, which I use here to turn on/off whether to audit the values. That way, if things get out of hand, it can be turned off quickly without having to change all the triggers.
It assumes that you do not delete records. That is what the CurrRec column is for. Just allow user to see those that are true. If you allow actual record deletes, a new level of code has to be used in the trigger. This code here does not include that scenario.
It automatically sets the values of LCHost, LCUser, and LCDate, in the trigger. These are the last change host (workstation used to make the change), last change user (user name of the person making the change), and the LCDate (date and time of the change), respectively.
Hope it helps.
USE master
CREATE DATABASE MyDatabase
USE MyDatabase
CREATE TABLE AppErrorLog
(EID int IDENTITY NOT NULL
CONSTRAINT PK_AppErrorLog_On_EID PRIMARY KEY CLUSTERED,
EDatedatetimeNOT NULL,
EUservarchar(50)NULL,
EMessagevarchar(1000)NULL,
ESource varchar(150)NOT NULL,
ENointNOT NULLDEFAULT 0,
ESeverityintNOT NULLDEFAULT 0,
ELineNo intNOT NULLDEFAULT 0,
EHost varchar(50)NULL
)
ON MyDatabase_data
GO
/* ======================================================================================== */
CREATE TABLE MiscValues
(MiscID intNOT NULL
CONSTRAINT PK_MiscValues_On_MiscID PRIMARY KEY CLUSTERED,
MiscDescvarchar(100)NULL,
MiscValuevarchar(200)NULL,
LCHostvarchar(50)NOT NULLDEFAULT HOST_NAME(),
LCUservarchar(50)NOT NULL DEFAULT USER,
LCDatedatetimeNOT NULL DEFAULT GETDATE()
)
ON MyDatabase_data
GO/* ========================================================================================== */
CREATE TABLE DropNote
(DropNoteID int IDENTITYNOT NULL
CONSTRAINT PK_DropNote_On_DropNoteID PRIMARY KEY CLUSTERED,
DropIDintNOT NULLDEFAULT 1
CONSTRAINT FK_DropNote_On_DropID
FOREIGN KEY (DropID)
REFERENCES Drop(DropID)
ON UPDATE CASCADE
ON DELETE CASCADE,
DropNoteDatedatetimeNOT NULLDEFAULT GETDATE(),
DropNotevarchar(400)NULL,
CurrRecbitNOT NULLDEFAULT (1),
SortOrderintNOT NULLDEFAULT 999,
LCHostvarchar(50)NOT NULLDEFAULT HOST_NAME(),
LCUservarchar(50)NOT NULLDEFAULT USER,
LCDatedatetimeNOT NULLDEFAULT GETDATE()
)
ON MyDatabase_data
GO
/*============================================================================================================================ */
CREATE TABLE dbo.DropNoteAudit
(
DropNoteAuditID int IDENTITY NOT NULL
CONSTRAINT PK_DropNoteAudit_On_DropNoteAuditID PRIMARY KEY CLUSTERED,
DropNoteID int,
DropID int,
DropNoteDate datetime,
DropNote varchar(400),
CurrRec bit,
SortOrder int,
LCHost varchar(50),
LCUser varchar(50),
LCDate datetime
)
ON MyDatabase_audit
GO
/* ================================================================================================== */
CREATE TRIGGER dbo.TG_Trigger_On_Table_DropNote
ON dbo.DropNote
FOR INSERT, UPDATE, DELETE
AS
BEGIN TRY
DECLARE @idvalint
DECLARE @CountDelint
DECLARE @CountInint
DECLARE @logauditactivitybit
DECLARE @mvaluevarchar(200)
SELECT @CountDel = COUNT(*) FROM Deleted
SELECT @CountIn = COUNT(*) FROM Inserted
SET @logauditactivity = 0
SELECT @mvalue=ISNULL(MiscValue,'') FROM MiscValues WHERE MiscDesc = 'LogAuditActivity'
if @mvalue = 'TRUE'
SET @logauditactivity = 1 -- set to true
if @CountDel = 0 and @CountIn = 1-- inserted
BEGIN
BEGIN TRANSACTION
UPDATE DropNote
SET LCDATE = GETDATE(),
LCUSER = USER,
LCHOST = HOST_NAME()
FROM dbo.DropNote s
JOIN inserted i
ON i.DropNoteID = s.DropNoteID
COMMIT TRANSACTION
END
IF @CountDel = 1 and @CountIn = 0 and @logauditactivity = 1-- deleted
BEGIN
BEGIN TRANSACTION
INSERT INTO DropNoteAudit
SELECT * FROM DELETED
COMMIT TRANSACTION
END
if @CountDel = 1 and @CountIn = 1-- updated
BEGIN
BEGIN TRANSACTION
if @logauditactivity = 1
BEGIN
INSERT INTO DropNoteAudit
SELECT * FROM DELETED
END
UPDATE DropNote
SET LCDATE = GETDATE(),
LCUSER = USER,
LCHOST = HOST_NAME()
FROM dbo.DropNote s
JOIN inserted i
ON i.DropNoteID = s.DropNoteID
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH
DECLARE@ErrorNoint,
@Severityint,
@Stateint,
@LineNoint,
@errmessagevarchar(1000)
SELECT@ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@errmessage = ERROR_MESSAGE()
ROLLBACK TRAN
INSERT INTO AppErrorLog
VALUES (GETDATE(), USER, @errmessage, 'TG_Trigger_On_Table_DropNote', @ErrorNo, @Severity, @LineNo, HOST_NAME())
END CATCH
GO
/* ====================================================================================================== */
--Set the value of LogAuditActivity to turn it on/off
INSERT INTO MiscValues VALUES (1,'LogAuditActivity','TRUE',HOST_NAME(),USER,GETDATE())
--Query to show all historical rows for given row id (current row, followed by all the historical rows, in descending order by date)
SELECT DropNoteID, DropID, DropNoteDate, DropNote, CurrRec, SortOrder, LCHost, LCUser, LCDate
FROM DropNote
WHERE DropNoteID=31271
UNION ALL
SELECT DropNoteID, DropID, DropNoteDate, DropNote, CurrRec, SortOrder, LCHost, LCUser, LCDate
FROM DropNote
WHERE DropNoteID=31271
ORDER BY LCDate DESC
September 15, 2011 at 9:50 am
Just a note on your solution. If you do hit the ROLLBACK TRANSACTION then the entire transaction, including the "Outer" or source transaction is rolled back, not just the actions in the trigger. Check out this blog post by Paul Randal. I also believe Gail Shaw (GilaMonster) recently posted an article here on SSC about this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 15, 2011 at 9:52 am
This is how I do it. See code below.
This will only work if you insert or update one row at a time, will it not?
John
September 15, 2011 at 9:58 am
Yes it will only work with one row because of:
@CountDel = 1 and @CountIn = 0
and
@CountDel = 1 and @CountIn = 1
If you changed that those to:
@CountDel > 0 and @CountIn = 0
and
@CountDel > 0 and @CountIn > 0
Then everything else would work with a set-based action.
Good catch. In my initial scan I missed that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 15, 2011 at 11:16 am
Thanks All!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply