Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

trigger problem Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 7:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
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
Post #1406727
Posted Monday, January 14, 2013 8:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1406750
Posted Monday, January 14, 2013 12:33 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:25 PM
Points: 2,044, Visits: 3,059

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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1406910
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse