July 26, 2017 at 7:01 am
Hi folks,
Please can you advise me on how to log an old value in a audit table via a trigger.
I current have a small table (Agreement_Funder) with 3 columns:
AgreementNumber
FunderName
TaggedDate
I have a trigger which fires a row into an audit table when the Agreement_Funder tables is updated.
The only rows that will be updated in the table are FunderName and TaggedDate.
Here is my trigger SQL:
ALTER TRIGGERTRIGGER [dbo] [dbo]..[tblTriggerAuditRecord] [tblTriggerAuditRecord] ONON [dbo] [dbo]..[tbl_Agreement_Funders][tbl_Agreement_Funders]AFTERAFTER UPDATEUPDATE,, INSERTINSERT
AS
AS
BEGIN
BEGININSERTINSERT INTOINTO tbl_Agreement_Funders_Audit tbl_Agreement_Funders_Audit((Agreement_NumberAgreement_Number,, Tagged_Date Tagged_Date,, Previous_Funder_Name Previous_Funder_Name,, Funder_Name Funder_Name,, UpdatedBy UpdatedBy,, UpdatedOn UpdatedOn))SELECTSELECT I I..Agreement_NumberAgreement_Number,, I I..Tagged_DateTagged_Date,, AF AF..Funder_NameFunder_Name,, I I..Funder_NameFunder_Name,, SUSER_SNAMESUSER_SNAME(),(), getdategetdate()() FROMFROM tbl_Agreement_Funders AF tbl_Agreement_Funders AFINNERINNER JOINJOIN INSERTED I INSERTED I ONON AF AF..Agreement_Number Agreement_Number == I I..Agreement_Number Agreement_Number
END
END
The trigger works perfectly but I would like to log the old value prior to the update. Please can someone advise me on the correct way to amend the trigger?
Thank you in advance.
DSC
July 26, 2017 at 7:16 am
Here is a template I generally use
😎
USE TEEST;
GO
SET NOCOUNT ON;
/*
--------------------------------------------------------------------------------------------------------------------------
Name: Generic Audit
Author: Eirikur Eiriksson
Platform: Microsoft SQL Server 2012 and later
Description: Generic auditing example where modifications to the data in the table
and the user information are logged including the data as it was before
the modification. The audit table also has a modification blocking and
any attempts to alter the date in the audit table are logged and blocked.
--------------------------------------------------------------------------------------------------------------------------
Objects:
1. dbo.TBL_TEST_AUDIT
The table to be audited.
2. dbo.TBL_GENERIC_AUDIT
The audit table, only one is needed for any number of tables using the generic
audit trigger.
3. dbo.TRG_AUDIT_TBL_GENERIC_AUDIT_READONLY
Modification Blocking trigger on dbo.TBL_GENERIC_AUDIT.
4. dbo.TRG_AUDIT_DBO_TBL_TEST_AUDIT
Audit trigger on dbo.TBL_TEST_AUDIT, a generic "one size fits all" trigger requiring
minimal changes for each implementation.
5. dbo.XUDF_GET_SESSION_CONNECTION_INFO
Session information function.
*/
--------------------------------------------------------------------------------------------------------------------------
-- Start of code
--------------------------------------------------------------------------------------------------------------------------
-- Rerunnable example code, drop any sample object if it exists.
IF OBJECT_ID('dbo.TBL_TEST_AUDIT') IS NOT NULL DROP TABLE dbo.TBL_TEST_AUDIT;
IF OBJECT_ID('dbo.TBL_GENERIC_AUDIT') IS NOT NULL DROP TABLE dbo.TBL_GENERIC_AUDIT;
IF OBJECT_ID('dbo.XUDF_GET_SESSION_CONNECTION_INFO') IS NOT NULL DROP FUNCTION dbo.XUDF_GET_SESSION_CONNECTION_INFO;
--------------------------------------------------------------------------------------------------------------------------
/* The function dbo.XUDF_GET_SESSION_CONNECTION_INFO returns all information
available from sys.dm_exec_connections and sys.dm_exec_sessions respectfully.
It is not called directly but used as a default value for the AUDIT_SESSION_XML
column in the dbo.TBL_GENERIC_AUDIT table.
*/
--------------------------------------------------------------------------------------------------------------------------
DECLARE @SQL_STR_001 NVARCHAR(MAX) = N'
CREATE FUNCTION dbo.XUDF_GET_SESSION_CONNECTION_INFO()
/* Returns a single node XML "RWLog" containing user, session, execution and relevant
system information as attributes for auditing and diagnostics/delbuging.
Not all the 70 columns will be needed at all times, feel free to comment out
the unnecessary ones from the sub-query.
*/
RETURNS XML
AS
BEGIN
RETURN
(
SELECT * FROM
(
SELECT
SCON.session_id
,SCON.most_recent_session_id
,SCON.connect_time
,SCON.net_transport
,SCON.protocol_type
,SCON.protocol_version
,SCON.endpoint_id
,SCON.encrypt_option
,SCON.auth_scheme
,SCON.node_affinity
,SCON.num_reads
,SCON.num_writes
,SCON.last_read
,SCON.last_write
,SCON.net_packet_size
,SCON.client_net_address
,SCON.client_tcp_port
,SCON.local_net_address
,SCON.local_tcp_port
,SCON.connection_id
,SCON.parent_connection_id
,SES.login_time
,SES.host_name
,SES.program_name
,SES.host_process_id
,SES.client_version
,SES.client_interface_name
,CONVERT(VARCHAR(100),SES.security_id,1) AS security_id
,SES.login_name
,SES.nt_domain
,SES.nt_user_name
,SES.status
,CONVERT(VARCHAR(100),SES.context_info,1) AS context_info
,SES.cpu_time
,SES.memory_usage
,SES.total_scheduled_time
,SES.total_elapsed_time
,SES.last_request_start_time
,SES.last_request_end_time
,SES.reads
,SES.writes
,SES.logical_reads
,SES.is_user_process
,SES.text_size
,SES.language
,SES.date_format
,SES.date_first
,SES.quoted_identifier
,SES.arithabort
,SES.ansi_null_dflt_on
,SES.ansi_defaults
,SES.ansi_warnings
,SES.ansi_padding
,SES.ansi_nulls
,SES.concat_null_yields_null
,SES.transaction_isolation_level
,SES.lock_timeout
,SES.deadlock_priority
,SES.row_count
,SES.prev_error
,CONVERT(VARCHAR(100),SES.original_security_id,1) AS original_security_id
,SES.original_login_name
,SES.last_successful_logon
,SES.last_unsuccessful_logon
,SES.unsuccessful_logons
,SES.group_id
,SES.database_id
,SES.authenticating_database_id
,SES.open_transaction_count
,CONVERT(VARCHAR(100),SCON.most_recent_sql_handle,1) AS most_recent_sql_handle
FROM sys.dm_exec_connections SCON
OUTER APPLY sys.dm_exec_sessions SES
WHERE SCON.session_id = @@SPID
AND SES.session_id = @@SPID
) AS RWLog
FOR XML AUTO
)
END
;
';
EXEC (@SQL_STR_001);
--------------------------------------------------------------------------------------------------------------------------
/*
Generic audit table
*/
--------------------------------------------------------------------------------------------------------------------------
CREATE TABLE dbo.TBL_GENERIC_AUDIT
(
AUDIT_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_GENERIC_AUDIT_AUDIT_ID PRIMARY KEY CLUSTERED WITH ( FILLFACTOR = 100, DATA_COMPRESSION = PAGE )
,AUDIT_TIMESTAMP DATETIME NOT NULL CONSTRAINT DFLT_DBO_TBL_GENERIC_AUDIT_AUDIT_TIMESTAMP DEFAULT ( GETDATE() )
,AUDIT_COL_IDENTITY INT NOT NULL
,AUDIT_ACTION_TYPE TINYINT NOT NULL
,AUDIT_OBJECT SYSNAME NOT NULL
,AUDIT_SESSION_XML XML NOT NULL CONSTRAINT DFLT_DBO_TBL_GENERIC_AUDIT_AUDIT_SESSION_XML DEFAULT ( dbo.XUDF_GET_SESSION_CONNECTION_INFO() )
,AUDIT_XML XML NOT NULL
);
--------------------------------------------------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER dbo.TRG_AUDIT_TBL_GENERIC_AUDIT_READONLY
ON dbo.TBL_GENERIC_AUDIT
/* The trigger dbo.TRG_AUDIT_TBL_GENERIC_AUDIT_READONLY will block any modifications
to the data in the dbo.TBL_GENERIC_AUDIT table and log the attempts to the audit.
As the data in the table will not change, the trigger only captures the values from
the inserted pseudo table which are the new values in an update attempt, the existing
values are in the table and can be linked to wia the AUDIT_COL_IDENTITY column.
*/
INSTEAD OF UPDATE
, DELETE
AS
BEGIN
INSERT INTO dbo.TBL_GENERIC_AUDIT (AUDIT_COL_IDENTITY,AUDIT_OBJECT,AUDIT_ACTION_TYPE,AUDIT_XML)
SELECT
D.AUDIT_ID
,N'dbo.TBL_GENERIC_AUDIT' AS AUDIT_OBJECT
,ISNULL((SIGN(I.AUDIT_ID) * 127),255)
,( SELECT
CASE
WHEN I.AUDIT_ID IS NOT NULL THEN 3
ELSE 2
END AS '@Type'
,(
SELECT
AUDIT_ID
,AUDIT_TIMESTAMP
,AUDIT_COL_IDENTITY
,AUDIT_ACTION_TYPE
FROM inserted B
WHERE A.AUDIT_ID = B.AUDIT_ID
FOR XML PATH('INSERTED'), TYPE,ELEMENTS XSINIL
)
FROM deleted A
WHERE D.AUDIT_ID = A.AUDIT_ID
FOR XML PATH('ACTION'),TYPE,ELEMENTS XSINIL
)
FROM deleted D
LEFT OUTER JOIN inserted I
ON D.AUDIT_ID = I.AUDIT_ID;
END
--------------------------------------------------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------------------------------------------------
/* The table dbo.TBL_TEST_AUDIT is the table to be audited.
All modifications to the data in this table will be recorded
in the dbo.TBL_GENERIC_AUDIT table.
*/
--------------------------------------------------------------------------------------------------------------------------
CREATE TABLE dbo.TBL_TEST_AUDIT
(
TEST_AUDIT_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_AUDIT_TEST_AUDIT_ID PRIMARY KEY CLUSTERED
,TEST_AUDIT_COL_1 INT NOT NULL
,TEST_AUDIT_COL_2 VARCHAR(50) NOT NULL
,TEST_AUDIT_COL_3 NVARCHAR(500) NULL
);
--------------------------------------------------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER dbo.TRG_AUDIT_DBO_TBL_TEST_AUDIT
ON dbo.TBL_TEST_AUDIT
/* Generic Audit Trigger
To implement in different tables, change the identity column
references marked in the code.
If any of the three actions does not require auditing
then remove it from here. Normally only Update and
Delete needs to be audited as the inserted data will
be un-touched.
*/
FOR
--INSERT, -- Auditing inserts is generally not necessary although
-- it may be a requirement in some cases. Often this depends
-- on the user information collected at the insert.
UPDATE, DELETE
AS
BEGIN
WITH IDENTITY_VALUES(ID_VAL, ACTION_TYPE) AS
(
SELECT
X.ID_VAL
,SUM(X.ACTION_TYPE) AS ACTION_TYPE
FROM
(
SELECT
------------------------------------------
-- Change I.TEST_AUDIT_ID reference if the
-- identity column has a different name.
------------------------------------------
I.TEST_AUDIT_ID AS ID_VAL
------------------------------------------
,CONVERT(TINYINT,1,0) AS ACTION_TYPE
FROM inserted I
UNION ALL
SELECT
------------------------------------------
-- Change I.TEST_AUDIT_ID reference if the
-- identity column has a different name.
------------------------------------------
D.TEST_AUDIT_ID AS ID_VAL
------------------------------------------
,CONVERT(TINYINT,2,0) AS ACTION_TYPE
FROM deleted D
) AS X
GROUP BY X.ID_VAL
)
INSERT INTO dbo.TBL_GENERIC_AUDIT (AUDIT_COL_IDENTITY,AUDIT_OBJECT,AUDIT_ACTION_TYPE,AUDIT_XML)
SELECT
X.ID_VAL
,N'dbo.TBL_TEST_AUDIT' AS AUDIT_OBJECT
,X.ACTION_TYPE
,(
SELECT
X.ACTION_TYPE AS '@Type'
---------------------------------------------------------------------------------
-- NOTE:
-- Uncomment this section only if there is a need to capture inserted data! as
-- enabling this section will result in multiple instances of duplicated data!
---------------------------------------------------------------------------------
--,(
-- SELECT
-- *
-- FROM inserted B
-- WHERE A.ID_VAL = B.TEST_AUDIT_ID
-- FOR XML PATH('INSERTED'), TYPE,ELEMENTS XSINIL
-- )
,(
SELECT
*
FROM deleted C
WHERE A.ID_VAL = C.TEST_AUDIT_ID
FOR XML PATH('DELETED'), TYPE,ELEMENTS XSINIL
)
FROM IDENTITY_VALUES A
WHERE X.ID_VAL = A.ID_VAL
FOR XML PATH('ACTION'),TYPE,ELEMENTS XSINIL
)
FROM IDENTITY_VALUES X;
END
--------------------------------------------------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------------------------------------------------
/* Testin the objects, first the audit functionality and then the modification blocking
*/
--------------------------------------------------------------------------------------------------------------------------
INSERT INTO dbo.TBL_TEST_AUDIT (TEST_AUDIT_COL_1,TEST_AUDIT_COL_2,TEST_AUDIT_COL_3)
VALUES (10,'ABC',N'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Quisque nec ligula libero.')
,(20,'DEF',N'Etiam pellentesque lacinia dui. Aliquam imperdiet, ligula faucibus imperdiet porta.')
,(30,'GHI',N'nunc est iaculis mi, ullamcorper dictum mi ipsum sit amet eros. Cras lacus maurise.')
,(40,'JKL',N'Uismod non elementum id, maximus ornare nisi. Cras ultrices, lorem eu aliquet susc.')
,(50,'MNO',N'Pit, lectus lacus cursus urna, ac varius lectus orci a arcu. Mauris sodales ligula.');
--------------------------------------------------------------------------------------------------------------------------
-- No modifications yet so nothing is returned by the first query unless the INSERT auditing
-- is turned on.
--------------------------------------------------------------------------------------------------------------------------
SELECT * FROM dbo.TBL_GENERIC_AUDIT;
--------------------------------------------------------------------------------------------------------------------------
-- Update every odd id number rows, the audit table will now have three rows with the before state of the data.
--------------------------------------------------------------------------------------------------------------------------
UPDATE TA
SET TA.TEST_AUDIT_COL_1 = TA.TEST_AUDIT_COL_1 * 3
,TA.TEST_AUDIT_COL_3 = N'Text removed!'
FROM dbo.TBL_TEST_AUDIT TA
WHERE TEST_AUDIT_ID % 2 = 1;
SELECT * FROM dbo.TBL_GENERIC_AUDIT;
--------------------------------------------------------------------------------------------------------------------------
-- Update every odd id number rows, the audit table will now have three rows with the before state of the data.
--------------------------------------------------------------------------------------------------------------------------
UPDATE TA
SET TA.TEST_AUDIT_COL_1 = TA.TEST_AUDIT_COL_1 * 3
,TA.TEST_AUDIT_COL_3 = N'Text changed the second time!'
FROM dbo.TBL_TEST_AUDIT TA
WHERE TEST_AUDIT_ID % 2 = 1;
SELECT * FROM dbo.TBL_GENERIC_AUDIT;
--------------------------------------------------------------------------------------------------------------------------
-- Update every odd id number rows, the audit table will now have three rows with the before state of the data.
--------------------------------------------------------------------------------------------------------------------------
UPDATE TA
SET TA.TEST_AUDIT_COL_1 = TA.TEST_AUDIT_COL_1 * 3
,TA.TEST_AUDIT_COL_3 = N'Text changed the third time!'
FROM dbo.TBL_TEST_AUDIT TA
WHERE TEST_AUDIT_ID % 2 = 1;
SELECT * FROM dbo.TBL_GENERIC_AUDIT;
--------------------------------------------------------------------------------------------------------------------------
-- Update every odd id number rows, the audit table will now have three rows with the before state of the data.
--------------------------------------------------------------------------------------------------------------------------
UPDATE TA
SET TA.TEST_AUDIT_COL_1 = TA.TEST_AUDIT_COL_1 * 3
,TA.TEST_AUDIT_COL_3 = NULL
FROM dbo.TBL_TEST_AUDIT TA
WHERE TEST_AUDIT_ID % 2 = 1;
SELECT * FROM dbo.TBL_GENERIC_AUDIT;
--------------------------------------------------------------------------------------------------------------------------
-- Update every odd id number rows, the audit table will now have three rows with the before state of the data.
--------------------------------------------------------------------------------------------------------------------------
UPDATE TA
SET TA.TEST_AUDIT_COL_1 = TA.TEST_AUDIT_COL_1 * 3
,TA.TEST_AUDIT_COL_3 = N'Text added after being nulled'
FROM dbo.TBL_TEST_AUDIT TA
WHERE TEST_AUDIT_ID % 2 = 1;
SELECT * FROM dbo.TBL_GENERIC_AUDIT;
--------------------------------------------------------------------------------------------------------------------------
-- Delete single row, a row is added to the audit table containing the values of the row deleted.
--------------------------------------------------------------------------------------------------------------------------
DELETE FROM dbo.TBL_TEST_AUDIT WHERE TEST_AUDIT_ID = 3;
SELECT * FROM dbo.TBL_GENERIC_AUDIT;
--------------------------------------------------------------------------------------------------------------------------
-- Attempt to delete everything from the audit table, the action is blocked and four rows added
-- to the table, one for each existing row. No data is collected in the autid table as it already
-- exists and will not change.
--------------------------------------------------------------------------------------------------------------------------
DELETE
FROM dbo.TBL_GENERIC_AUDIT;
SELECT * FROM dbo.TBL_GENERIC_AUDIT;
--------------------------------------------------------------------------------------------------------------------------
-- Attempt to update a single row, the action is blocked and one row added to the audit table.
-- This time the new or attempted values are captured in the AUDIT_XML column.
--------------------------------------------------------------------------------------------------------------------------
UPDATE dbo.TBL_GENERIC_AUDIT SET AUDIT_ACTION_TYPE = 0 WHERE AUDIT_ID = 5;
SELECT * FROM dbo.TBL_GENERIC_AUDIT;
--------------------------------------------------------------------------------------------------------------------------
-- Audit history query for the table dbo.TBL_TEST_AUDIT showing before and after values for
-- each modification.
--------------------------------------------------------------------------------------------------------------------------
;WITH BASE_AUDIT_DATA AS
(
SELECT
GA.AUDIT_ID
,GA.AUDIT_TIMESTAMP
,GA.AUDIT_COL_IDENTITY
,CASE
WHEN GA.AUDIT_ACTION_TYPE = 3 THEN 'UPDATED'
ELSE 'DELETED'
END AS AUDIT_ACTION_TYPE_NAME
,GA.AUDIT_OBJECT
,GA.AUDIT_XML.value('(ACTION/DELETED/TEST_AUDIT_COL_1/text())[1]','INT') AS BEFORE_TEST_AUDIT_COL_1
,GA.AUDIT_XML.value('(ACTION/DELETED/TEST_AUDIT_COL_2/text())[1]','VARCHAR(50)') AS BEFORE_TEST_AUDIT_COL_2
,GA.AUDIT_XML.value('(ACTION/DELETED/TEST_AUDIT_COL_3/text())[1]','NVARCHAR(500)') AS BEFORE_TEST_AUDIT_COL_3
,TA.TEST_AUDIT_COL_1 AS AFTER_TEST_AUDIT_COL_1
,TA.TEST_AUDIT_COL_2 AS AFTER_TEST_AUDIT_COL_2
,TA.TEST_AUDIT_COL_3 AS AFTER_TEST_AUDIT_COL_3
,GA.AUDIT_SESSION_XML.value('(RWLog/@host_name)[1]','NVARCHAR(1024)') AS AUDIT_USER_host_name
,GA.AUDIT_SESSION_XML.value('(RWLog/@original_login_name)[1]','NVARCHAR(1024)') AS AUDIT_USER_original_login_name
,GA.AUDIT_SESSION_XML.value('(RWLog/@login_name)[1]','NVARCHAR(1024)') AS AUDIT_USER_login_name
,GA.AUDIT_SESSION_XML.value('(RWLog/@nt_domain)[1]','NVARCHAR(1024)') AS AUDIT_USER_nt_domain
,GA.AUDIT_SESSION_XML.value('(RWLog/@nt_user_name)[1]','NVARCHAR(1024)') AS AUDIT_USER_nt_user_name
,GA.AUDIT_SESSION_XML.value('(RWLog/@client_net_address)[1]','NVARCHAR(1024)') AS AUDIT_USER_client_net_address
,GA.AUDIT_SESSION_XML.value('(RWLog/@program_name)[1]','NVARCHAR(1024)') AS AUDIT_USER_program_name
,GA.AUDIT_SESSION_XML.value('(RWLog/@client_interface_name)[1]','NVARCHAR(1024)') AS AUDIT_USER_client_interface_name
FROM dbo.TBL_GENERIC_AUDIT GA
LEFT OUTER JOIN dbo.TBL_TEST_AUDIT TA
ON GA.AUDIT_COL_IDENTITY = TA.TEST_AUDIT_ID
WHERE GA.AUDIT_OBJECT = N'dbo.TBL_TEST_AUDIT'
)
SELECT
BAD.AUDIT_ID
,BAD.AUDIT_TIMESTAMP
,BAD.AUDIT_COL_IDENTITY
,BAD.AUDIT_ACTION_TYPE_NAME
,BAD.AUDIT_OBJECT
,BAD.BEFORE_TEST_AUDIT_COL_1
,BAD.BEFORE_TEST_AUDIT_COL_2
,BAD.BEFORE_TEST_AUDIT_COL_3
,LEAD(BAD.BEFORE_TEST_AUDIT_COL_1,1,BAD.AFTER_TEST_AUDIT_COL_1) OVER
(
PARTITION BY BAD.AUDIT_OBJECT
,BAD.AUDIT_COL_IDENTITY
ORDER BY BAD.AUDIT_ID
) AS AFTER_TEST_AUDIT_COL_1
,LEAD(BAD.BEFORE_TEST_AUDIT_COL_2,1,BAD.AFTER_TEST_AUDIT_COL_2) OVER
(
PARTITION BY BAD.AUDIT_OBJECT
,BAD.AUDIT_COL_IDENTITY
ORDER BY BAD.AUDIT_ID
) AS AFTER_TEST_AUDIT_COL_2
,LEAD(BAD.BEFORE_TEST_AUDIT_COL_3,1,BAD.AFTER_TEST_AUDIT_COL_3) OVER
(
PARTITION BY BAD.AUDIT_OBJECT
,BAD.AUDIT_COL_IDENTITY
ORDER BY BAD.AUDIT_ID
) AS AFTER_TEST_AUDIT_COL_3
,BAD.AUDIT_USER_host_name
,BAD.AUDIT_USER_original_login_name
,BAD.AUDIT_USER_login_name
,BAD.AUDIT_USER_nt_domain
,BAD.AUDIT_USER_nt_user_name
,BAD.AUDIT_USER_client_net_address
,BAD.AUDIT_USER_program_name
,BAD.AUDIT_USER_client_interface_name
FROM BASE_AUDIT_DATA BAD;
--------------------------------------------------------------------------------------------------------------------------
-- End of code
--------------------------------------------------------------------------------------------------------------------------
July 26, 2017 at 7:22 am
Just in case it's a possibility for you, if you were to move to SQL Server 2016, you may find that temporal tables would provide the necessary functionality without requiring much work on your part.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 26, 2017 at 7:37 am
Phil Parkin - Wednesday, July 26, 2017 7:22 AMJust in case it's a possibility for you, if you were to move to SQL Server 2016, you may find that temporal tables would provide the necessary functionality without requiring much work on your part.
Good point but it will not record who/which connection made the changes.
😎
July 26, 2017 at 9:54 am
If you add a modifiedby column defaulted to suser() to your temporal table it will capture the login. Your application could override the default where authorization is controlled in the application. Changes outside the app can be captured by the default. Either way who changes data is written to the history table.
July 27, 2017 at 1:51 am
Thanks guys! We are in the process of upgrading to 2016 so I might just wait it out and use the advanced features.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply