USE [eForms]
GO
/****** Object:  Trigger [dbo].[RoutePlan_AuditTrigger]    Script Date: 9/25/2014 12:36:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[RoutePlan_AuditTrigger] ON [dbo].[RoutePlan]
    FOR INSERT, UPDATE, DELETE
AS
    BEGIN

        DECLARE @s_RowAction VARCHAR(16)
        DECLARE @i_DeleteCount INT
        DECLARE @i_InsertCount INT

        SELECT  @i_DeleteCount = COUNT(*)
        FROM    DELETED
        SELECT  @i_InsertCount = COUNT(*)
        FROM    INSERTED

        IF @i_DeleteCount = 0
            AND @i_InsertCount > 0
            SET @s_RowAction = 'INSERT'
        IF @i_DeleteCount > 0
            AND @i_InsertCount > 0
            SET @s_RowAction = 'UPDATE'
        IF @i_DeleteCount > 0
            AND @i_InsertCount = 0
            SET @s_RowAction = 'DELETE'


        IF @s_RowAction = 'INSERT'
            OR @s_RowAction = 'UPDATE'
            INSERT  INTO RoutePlanAudit(
                    RoutePlanId ,
                    FormId ,
                    UserId ,
                    Rank ,
                    RoutePlanStatusId ,
                    StatusDate ,
                    Notes ,
                    IsStrict ,
                    RoutePlanResponsibilityId ,
                    IsPostApproval ,
                    ProcessedByUserId ,
                    Deleted ,
                    RowAction ,
                    ModifiedById ,
                    ModifiedDate
                )
                    SELECT  RoutePlanId ,
                            FormId ,
                            UserId ,
                            Rank ,
                            RoutePlanStatusId ,
                            StatusDate ,
                            Notes ,
                            IsStrict ,
                            RoutePlanResponsibilityId ,
                            IsPostApproval ,
                            ProcessedByUserId ,
                            Deleted ,
                            @s_RowAction ,
                            ISNULL(ModifiedById,CreatedById) ,
                            GETDATE()
                    FROM    INSERTED

        IF @s_RowAction = 'DELETE'
            INSERT  INTO RoutePlanAudit(
                    RoutePlanId ,
                    FormId ,
                    UserId ,
                    Rank ,
                    RoutePlanStatusId ,
                    StatusDate ,
                    Notes ,
                    IsStrict ,
                    RoutePlanResponsibilityId ,
                    IsPostApproval ,
                    ProcessedByUserId ,
                    Deleted ,
                    RowAction ,
                    ModifiedById ,
                    ModifiedDate
                )
                    SELECT  RoutePlanId ,
                            FormId ,
                            UserId ,
                            Rank ,
                            RoutePlanStatusId ,
                            StatusDate ,
                            Notes ,
                            IsStrict ,
                            RoutePlanResponsibilityId ,
                            IsPostApproval ,
                            ProcessedByUserId ,
                            Deleted ,
                            @s_RowAction ,
                            ISNULL(ModifiedById,CreatedById) ,
                            GETDATE()
                    FROM    DELETED

    END

    SET ANSI_NULLS ON