October 12, 2016 at 5:45 pm
Hi,
Im inserting data into audit table before update and after update is there any other effective method.
Create trigger trg_employee
on employee
AFTER UPDATE
AS
Declare @operation_cd char(1);
IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
BEGIN
SET @operation_Cd='U'
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
DROP TABLE #Results
IF OBJECT_ID('tempdb..#Results1') IS NOT NULL
DROP TABLE #Results1
select checksum(id,name,phone) as checksum,id,name,phone
into #results
from inserted
select checksum(id,name,phone) as checksum,id,name,phone
into #results1
from deleted
delete from inserted i
join deleted d
on i.checksum=d.checksum
insert into audit(id,name,phone,operation)
select id,name,phone,@operation_cd from inserted
if not exists(select 1 from inserted i join deleted d on i.checksum=d.checksum)
join deleted d
insert into audit(id,name,phone,operation)
select id,name,phone,'o' from deleted
END
October 12, 2016 at 10:54 pm
@Patrick123 (10/12/2016)
Hi,Im inserting data into audit table before update and after update is there any other effective method.
Don't audit INSERTs or the "INSERTED" side of UPDATES. Inserts will be available in the original table. Auditing them will cause the immediate and unnecessary duplication of data. Auditing the INSERTED side of updates is also an instant and unnecessary duplication of data.
Only audit from the DELETED logical table in the trigger. If you audit from both the INSERTED and DELETED tables, you will effectively triplicate ever row in the original table for every insert and duplicate every row that is changed.
Never materialize either logical table in a Temp Table or Table Variable unless you like really slow performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2016 at 2:18 am
Further on Jeff's fine reply, here is an example of "Generic" table auditing with few extras for education and entertainment.
😎
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
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
--------------------------------------------------------------------------------------------------------------------------
October 13, 2016 at 12:20 pm
Thanks for the comments
October 13, 2016 at 12:27 pm
Have you considered getting a third party audit tool? Why reinvent the wheel, expose yourself to legal problems, and slow your overall performance?
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
October 13, 2016 at 12:57 pm
You'll want to match directly on id, then you can use CHECKSUM across the other columns. Remember to always think set-based when possible, to get max benefits from SQL's processing.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER trg_employee
ON dbo.employee
AFTER UPDATE
AS
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
BEGIN
INSERT INTO dbo.audit(id,name,phone,operation)
SELECT
CASE WHEN operation = 'U' THEN i.id ELSE d.id END AS id,
CASE WHEN operation = 'U' THEN i.name ELSE d.name END AS name,
CASE WHEN operation = 'U' THEN i.phone ELSE d.phone END AS phone,
operation
FROM inserted i
INNER JOIN deleted d ON i.id = d.id AND checksum(i.name,i.phone) <> checksum(i.name,i.phone)
CROSS JOIN (
VALUES('O','U')
) AS operations(operation)
END
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 14, 2016 at 3:57 pm
thanks
October 14, 2016 at 5:40 pm
I would never use CHECKSUM for such a thing. It's much too easy for it to equate two things that are not equal because it's just a simple binary adder underneath. Here's a simple example...
SELECT CS1 = CHECKSUM('A352KD')
,CS2 = CHECKSUM('A352NT')
;
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2016 at 11:21 pm
CELKO (10/13/2016)
Have you considered getting a third party audit tool? Why reinvent the wheel, expose yourself to legal problems, and slow your overall performance?
I've found that most 3rd party audit tools are relatively terrible for performance and some don't even understand the concept that each action can result in more than one row.
Do you have a recommendation for such a tool? I'd love to check it for the aforementioned problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2016 at 11:23 pm
@Patrick123 (10/14/2016)
thanks
I see that you've marked the checksum method as your choice. Please, see my warning about the checksum method a little further up in this discussion.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2016 at 11:29 pm
Eirikur Eiriksson (10/13/2016)
Further on Jeff's fine reply, here is an example of "Generic" table auditing with few extras for education and entertainment.
Correct me if I'm wrong, please. It would appear that you're calculating the action type for every row in the logical tables. If that's correct, then you could optimize the trigger by making the realization that there can be one and only one action during the firing of a trigger regardless of the number of rows.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 19, 2016 at 12:19 am
Jeff Moden (10/18/2016)
Eirikur Eiriksson (10/13/2016)
Further on Jeff's fine reply, here is an example of "Generic" table auditing with few extras for education and entertainment.Correct me if I'm wrong, please. It would appear that you're calculating the action type for every row in the logical tables. If that's correct, then you could optimize the trigger by making the realization that there can be one and only one action during the firing of a trigger regardless of the number of rows.
Very close although the calculation is probably not noticeable given what the sub-query is doing. The trigger is designed to capture inserts if desired which means it has to scan both I & D for identity values. If the option of capturing inserts is removed then this can be simplified to switching constant and a scan of the Deleted table. The only reason why I include the insert option is that I've had that kind of requirements quite often for configuration and settings type of tables.
😎
The audit trigger without the option of catching inserts.
--------------------------------------------------------------------------------------------------------------------------
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 either of the two actions does not require auditing
then remove it from here.
*/
FOR
UPDATE, DELETE
AS
BEGIN
WITH IDENTITY_VALUES(ID_VAL, ACTION_TYPE) AS
(
SELECT
DLTBL.ID_VAL
,X.ACTION_TYPE
FROM
(
SELECT
D.TEST_AUDIT_ID AS ID_VAL
FROMdeletedD
) AS DLTBL
CROSS APPLY
(
SELECT
CASE WHEN EXISTS (SELECT * FROM inserted) THEN 3
ELSE 2
END AS ACTION_TYPE
) X(ACTION_TYPE)
)
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'
,(
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
--------------------------------------------------------------------------------------------------------------------------
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply