CODEWITHSUNDEEP
sql-servertriggers
Brandon White
Brandon White

Reputation: 103

Insert Statement not working for Insert trigger

I have a trigger that executes off the back of a stored procedure, to capture certain data changes and inserts, for audit purposes. There is a stored procedure that adds rows to table DTA, the trigger is coded to fire from this as such;

CREATE TRIGGER [AUDIT_TRACE]
    ON [DTA]
    AFTER UPDATE, INSERT
AS BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION

        IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tmp_inserted')
            DROP TABLE tmp_inserted 
        IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tmp_deleted')
            DROP TABLE tmp_deleted 
        SELECT * INTO tmp_inserted from inserted
        SELECT * INTO tmp_deleted from deleted

        INSERT INTO [AUDIT_TRAIL]
        SELECT 
            UpdatedDate
            ,UserName
            ,Name
            ,oldValue
            ,newValue
            ,DATATABLEID
            ,ISNULL(AuthInvNo,'')+ISNULL(invNO,'') as InvoiceNumber
            ,AuthAccount As Product
            ,AuthValue AS Value
            ,QTY
            ,InputScreen
        FROM 
        (
            SELECT 
                i.UpdatedDate as [UpdatedDate]
                ,psn.UserName as [Username]
                ,CONCAT(psn.Firstname,' ',psn.surname) as [Name]
                ,CONVERT(nvarchar(36),i.DataTableId) as [DataTableID]
                ,dtType.Description as [InputScreen]
                ,dtat.Description as [ColumnName]
                ,CONVERT(nvarchar(1000),dtText.Text) as [Entry]
                ,dtavB.Description as OldValue
                ,dtavA.Description as NewValue
            FROM dt
            INNER JOIN inserted i on i.DataTableId = dt.DataTableId
            LEFT JOIN deleted d on d.DataTableId = i.DataTableId
            INNER JOIN dtavA on dtavA.DataTableAttributeValueId = i.DataTableAttributeValueId
                                and dtavA.DataTableAttributeTypeId IN ('23087D97-B96B-4015-9E66-258EE7CAF499','2D5E9D64-A2B6-444D-938A-7D8DD66208E0')-- after
            LEFT JOIN dtavB on dtavB.DataTableAttributeValueId = d.DataTableAttributeValueId
                                and dtavB.DataTableAttributeTypeId IN ('23087D97-B96B-4015-9E66-258EE7CAF499','2D5E9D64-A2B6-444D-938A-7D8DD66208E0')-- before
            INNER JOIN dtText on dtText.DataTableId = i.DataTableId
            INNER JOIN dtType on dtType.DataTableTypeId = dt.DataTableTypeID
            INNER JOIN psn on psn.PersonId = i.UpdatedBy
            INNER JOIN dtat on dtat.DataTableAttributeTypeId = dtText.DataTableAttributeTypeId
        )E
        PIVOT(MAX([ENTRY]) FOR [COLUMNNAME] IN(DEBITCREDIT,AuthValue,QTY,AuthAccount,AuthInvNo,InvNO))as p
    COMMIT TRANSACTION
END

Now the problem is that when inserting data into the DTA table nothing is being inserted into our AUDIT_TRAIL table, but when a row is updated in the DTA table, the outcome is exactly what we expect, oldValue, NewValue and all. As far as my colleagues and I can tell there is nothing wrong with the query, we have used profiler traces and all the sections are executing as they should. When running the code manually to select from the tmp_Inserted and tmp_Deleted tables, so we can see what the values we are dealing with are, there is again no issue. To further complicate things, when the INSERT INTO statement is run on its own then the newly inserted line appears as we would expect.

Upvotes: 0

Views: 956

Answers (1)

Swapnil
Swapnil

Reputation: 434

In order to ensure that data is not filtered by the JOINs in insert statement within trigger, insert data from INSERTED by joining it with all tables in inline INSERT. 

SELECT I.* INTO tmp_inserted
FROM dt
INNER JOIN inserted i on i.DataTableId = dt.DataTableId
LEFT JOIN deleted d etc..

Upvotes: 1

Related Questions