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: Monday, June 8, 2015 9:14 AM
Points: 145, Visits: 349
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 @ 3:08 PM
Points: 14,542, Visits: 14,314
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
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 3,033, Visits: 4,641

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)

"If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them." James Blackburn, in closing argument in the "Fatal Vision" murders trial
Post #1406910
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse