Create the following Table which you want to
audit:
IF EXISTS (SELECT * FROM sysobjects
WHERE id =
OBJECT_ID(N'[dbo].[test_audit]')
AND
OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE [dbo].[test_audit]
GO
CREATE TABLE test_audit (
id
INTEGER NOT NULL,
credat SMALLDATETIME NOT NULL,
creusr VARCHAR(8) NOT
NULL,
mutdat VARCHAR(500),
mutusr VARCHAR(45)
)
GO
Create the following helper function to get the
login name. If Windows-Authentication is used,
the domain-part is truncated (e.g. \\SERVER\Zahn ==> Zahn)
IF EXISTS (SELECT * FROM sysobjects
WHERE id =
OBJECT_ID(N'[dbo].[get_login_name]')
AND
OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
DROP FUNCTION [dbo].[get_login_name]
GO
/*
* ==============================================================================
* Function: get_login_name
*
* Returns: Name of the logged-in user. If Windows-Authentication is used,
* the domain-part is
truncated (e.g. \\SERVER\Zahn ==> Zahn)
* ==============================================================================
*/
CREATE FUNCTION get_login_name()
RETURNS VARCHAR(8)
AS
BEGIN
RETURN
RIGHT(SYSTEM_USER,(LEN(SYSTEM_USER)-CHARINDEX('\',SYSTEM_USER)))
END
GO
Add the following Defaults for the columns
credat and creusr.
ALTER TABLE test_audit ADD
CONSTRAINT default_credat DEFAULT (getdate()) FOR credat,
CONSTRAINT default_erfusr DEFAULT (dbo.get_login_name()) FOR
creusr
GO
Create the following helper function, to count the
number of substrings in a string.
IF EXISTS (SELECT * FROM sysobjects
WHERE id =
OBJECT_ID(N'[dbo].[cnt_strings]')
AND
OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
DROP FUNCTION [dbo].[cnt_strings]
GO
/*
* ==============================================================================
* Function: cnt_strings
*
* Arguments: @substring: String to be count
*
@string: String in which to count
*
* Returns: The number of Occurrences of Substring in String.
* ==============================================================================
*/
CREATE FUNCTION cnt_strings (@substring VARCHAR(5), @string VARCHAR(300))
RETURNS SMALLINT AS
BEGIN
-- Position in String
DECLARE @pos SMALLINT
-- Count the Occurrences of Substring in String
DECLARE @count SMALLINT
-- Length of Substring and
String
DECLARE @lenString SMALLINT
DECLARE @lenSubString SMALLINT
-- Initializing
set @pos = -1
set @count = 0
set @lenString = LEN(@string)
set @lenSubString = LEN(@substring)
-- Loop through the String and count
Occurences of Substring
WHILE (@pos <> 0)
BEGIN
SET @pos =
CHARINDEX(@substring,@string,@pos)
IF (@pos = 0) BREAK
-- Substring was
found
SET @pos = @pos + @lenSubstring
SET @count = @count +1
END
RETURN @count
END
GO
Create the main audit function, which manipulates
the update-string:
/*
* ==============================================================================
* Function: log_audit
*
* Arguments: @old_value: string containing @separator separated substrings
* @separator: string separator
* @new_value: value to be appended
*
* Returns: Table Audtiting - The Columns mutdat and mutusr reflects any
* updates on the Table. These columns contains a ";" separated
* String with the Mutations-User / Mutations-Date. The last update
* is on the most right side, max num_entries Updates are seen.
* If there are more than 10 Updates the most left entry is deleted
* from the string.
* ==============================================================================
*/
CREATE FUNCTION log_audit (@old_value VARCHAR(500), @separator VARCHAR(5), @new_value VARCHAR(20))
RETURNS VARCHAR(500) AS
BEGIN
DECLARE @first_occurrence SMALLINT
DECLARE @num_entries SMALLINT
SET @num_entries = 10
-- Return new_value if multi occurrence field is empty IF (@old_value IS NULL)
BEGIN
RETURN @new_value
END
-- Remove first occurrence (most left) if max number of entries are reached IF (dbo.cnt_strings (@separator, @old_value) >= (@num_entries - 1))
BEGIN
-- @first_occurrence contains position of first semicolon.
-- The position is counted from the right side,
-- as needed in the right function. SET @first_occurrence = LEN(@old_value) - CHARINDEX(@separator, @old_value)
SET @old_value = RIGHT(@old_value, @first_occurrence)
END
RETURN @old_value+@separator+@new_value
END
GO
Finally create the following UPDATE Trigger. Triggers make use of
two special tables called inserted and deleted. The inserted table contains the
data referenced in an INSERT before it is actually committed to the database. The deleted
table contains the data in the underlying table referenced in a DELETE before it is
actually removed from the database. When an UPDATE is issued both tables are used. More
specifically, the new data referenced in the UPDATE statement is contained in inserted
and the data that is being updated is contained in deleted.
IF EXISTS (SELECT * FROM sysobjects
WHERE id =
OBJECT_ID(N'[dbo].[update_log_test_audit]')
AND
OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [dbo].[update_log_test_audit]
GO
CREATE TRIGGER update_log_test_audit
ON dbo.test_audit FOR UPDATE
NOT FOR REPLICATION AS
UPDATE test_audit SET
test_audit.mutdat =
dbo.log_audit(deleted.mutdat,'|',CONVERT(VARCHAR(20),GETDATE(),120)),
test_audit.mutusr = dbo.log_audit(deleted.mutusr,'|',dbo.get_login_name())
FROM inserted, deleted
WHERE test_audit.id = inserted.id
GO
Insert some values into the test table, note that we
set @num_entries = 3 for this test:
INSERT INTO test_audit (id) VALUES (1)
INSERT INTO test_audit (id) VALUES (2)
INSERT INTO test_audit (id) VALUES (3)
INSERT INTO test_audit (id) VALUES (4)
INSERT INTO test_audit (id) VALUES (5)
GO
SELECT * FROM test_audit
GO
The initial values for credat and
creusr are inserted by the Defaults. Now update row 2:
UPDATE test_audit SET id = 6 WHERE id = 2
GO
Update the test table as follows:
UPDATE test_audit SET id = 2 WHERE id = 6
GO
Now, two entries are included in mutdat and
mutusr.
UPDATE test_audit SET id = 6 WHERE id = 2
GO
SELECT id,mutdat,mutusr FROM test_audit
GO
UPDATE test_audit SET id = 2 WHERE id = 6
GO
SELECT id,mutdat,mutusr FROM test_audit
GO
In the last screendump, you can see, that the first entry
"2003-10-02 11:30:51" is disappered from the string, it was shifted out of the
string.
