trigger problem

  • Anyone see a problem with this trigger?, rather complex, but the issue may be in the logic of:

    "WHERE INSERTED.Transaction_Type = 'Transfer' and INSERTED.TransferToCostCenter <> INSERTED.TransferFromCostCenter"

    Full SQL below:

    USE [TrackIT]

    GO

    IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trPopulateJournalEntriesForTransfers]'))

    DROP TRIGGER [dbo].[trPopulateJournalEntriesForTransfers]

    GO

    USE [TrackIT]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [dbo].[trPopulateJournalEntriesForTransfers]

    ON [dbo].[tblTransactions]

    AFTER INSERT

    AS

    BEGIN

    INSERT INTO tblSWJournal

    (tblSWJournal.Description,

    tblSWJournal.Amt,

    tblSWJournal.Cost_Center,

    tblSWJournal.Ledger,

    tblSWJournal.Book,

    tblSWJournal.Currency,

    tblSWJournal.Account,

    tblSWJournal.Date)

    SELECT

    INSERTED.PO_Number + RTRIM(LEFT(INSERTED.EndUserLastName,6)) + INSERTED.SoftwareShortName + INSERTED.HeatTicketNumber,

    (INSERTED.Unit_Price * -1),

    '823008',

    'ACTUALS',

    'C',

    'USD',

    '153355930',

    INSERTED.Transaction_Date

    FROM INSERTED

    WHERE INSERTED.Transaction_Type = 'Transfer' and INSERTED.TransferToCostCenter <> INSERTED.TransferFromCostCenter

    UNION ALL

    SELECT

    INSERTED.PO_Number + RTRIM(LEFT(INSERTED.EndUserLastName,6)) + INSERTED.SoftwareShortName + INSERTED.HeatTicketNumber,

    INSERTED.Unit_Price,

    INSERTED.AllocationCostCenter,

    'ACTUALS',

    'C',

    'USD',

    '60011060',

    INSERTED.Transaction_Date

    FROM INSERTED

    WHERE INSERTED.Transaction_Type = 'Transfer' and INSERTED.TransferToCostCenter <> INSERTED.TransferFromCostCenter

    END

    GO

  • briancampbellmcad (1/14/2013)


    Anyone see a problem with this trigger?, rather complex, but the issue may be in the logic of:

    "WHERE INSERTED.Transaction_Type = 'Transfer' and INSERTED.TransferToCostCenter <> INSERTED.TransferFromCostCenter"

    Full SQL below:

    Syntactically there is nothing wrong here. Perhaps if you explained what it is not doing we might be able to help. Keep in mind we can't see your screen and we have no knowledge of your project.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Any chance that either or both "TransferToCostCenter" and "TransferFromCostCenter" are NULL?

    Not equal won't work on NULL; you'll have to explicitly allow for NULL in the comparison.

    For example:

    WHERE ...

    AND ISNULL(INSERTED.TransferToCostCenter, '-1') <> ISNULL(INSERTED.TransferFromCostCenter, '-1')

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply