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
--------------------------------------------------------------------------------------------------------------------------