January 14, 2013 at 7:35 am
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
January 14, 2013 at 8:03 am
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/
January 14, 2013 at 12:33 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply