For a while now, business have been more aware of the importance of protecting their data, at least from unwanted changes or deletions. I will not go into the specifics of which method to use, whether to comply with an international standard or not, or even whether to use in-house development or purchase an off-the-shelf product. There is a multitude of information on the web.
What you will find below is a script template on one of the approaches we used to audit data using triggers. Our approach was to create an identical set of audit tables for each table that was identified to hold the “more important” information, then use the below sample to generate the triggers.
The first step is to create the base tables.
/* SET UP TABLES */
CREATE TABLE dbo.tb_source (
s_pk INT IDENTITY(1,1) NOT NULL,
s_desc VARCHAR(50) NOT NULL
);
GO
CREATE TABLE dbo.tb_source_audit (
d_pk INT IDENTITY(1,1) NOT NULL,
d_action smallint NOT NULL,
d_spk INT NOT NULL,
d_sdesc VARCHAR(50) NOT NULL
);
GO
Next we’ll create the actual audit trigger.
/* SET UP AUDIT TRIGGER */
CREATE TRIGGER trg_auditsource ON tb_source
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
DECLARE @insert_action smallint,
@update_action smallint,
@delete_action smallint
BEGIN
SELECT
@insert_action = 1,
@update_action = 2,
@delete_action = 3;
-- check for updates
IF EXISTS(SELECT * FROM DELETED)
BEGIN
-- filter deletes only
INSERT INTO dbo.tb_source_audit (d_action, d_spk, d_sdesc)
SELECT @delete_action, s_pk, s_desc
FROM DELETED
WHERE s_pk NOT IN (SELECT s_pk FROM INSERTED);
-- filter updates
INSERT INTO dbo.tb_source_audit (d_action, d_spk, d_sdesc)
SELECT @update_action, s_pk, s_desc
FROM INSERTED
WHERE s_pk IN (SELECT s_pk FROM DELETED);
END
ELSE
-- inserts only
BEGIN
INSERT INTO dbo.tb_source_audit (d_action, d_spk, d_sdesc)
SELECT @insert_action, s_pk, s_desc
FROM INSERTED;
END
END
GO
Now let’s test whether the functionality has been achieved. The first test is a simple one-row insert.
INSERT INTO dbo.tb_source VALUES ('item 1');
-- 1 record inserted in source table
-- 1 record inserted in audit table
After each step, check that the contents of the source and audit tables are as expected.
SELECT * FROM dbo.tb_source;
SELECT * FROM dbo.tb_source_audit;
Next we’ll try with a multiple-row insert.
INSERT INTO dbo.tb_source
SELECT 'item 2' UNION ALL
SELECT 'item 3' UNION ALL
SELECT 'item 4' UNION ALL
SELECT 'item 5' UNION ALL
SELECT 'item 6';
-- 5 records inserted in source table
-- 5 records inserted in audit table
So far it looks like it’s working correctly. With the next tests we shall be verifying single and multiple-row updates.
UPDATE dbo.tb_source SET s_desc = 'modified item 5' WHERE s_desc = 'item 5';
-- 1 record updated in source table
-- 1 record updated in audit table
Multiple-row update:
UPDATE dbo.tb_source SET s_desc = 'same desc' WHERE s_pk IN (2,3);
-- 2 records updated in source table
-- 2 records updated in audit table
In the last of the tests we shall verify that single and multiple-row deletes are audited.
DELETE FROM dbo.tb_source WHERE s_pk = 4;
-- 1 record deleted from source table
-- 1 record deleted from audit table
Multiple-row delete:
DELETE FROM dbo.tb_source WHERE s_pk IN (1, 6);
-- 2 records deleted from source table
-- 2 records deleted from audit table
Finally, we remove the tables used for this testing.
/* CLEAN UP */
DROP TABLE dbo.tb_source;
DROP TABLE dbo.tb_source_audit;
GO
As explained earlier, this is just one of the approaches that can be implemented to audit data. The entire script for the above can be downloaded here: sample_trigger_multiple_actions.zip.