USE Curia
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO

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

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

ALTER TABLE test_audit ADD
    CONSTRAINT default_credat DEFAULT (getdate()) FOR credat,
    CONSTRAINT default_erfusr DEFAULT (dbo.get_login_name()) FOR creusr
GO

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

IF EXISTS (SELECT * FROM sysobjects
            WHERE id = OBJECT_ID(N'[dbo].[log_audit]')
              AND OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
DROP FUNCTION [dbo].[log_audit]
GO

/*
* ==============================================================================
* 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

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

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

DELETE FROM test_audit
GO

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