trigger problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » trigger problem

trigger problem

Rate Topic

Display Mode

Topic Options

Author

Message

briancampbellmcad

Posted Monday, January 14, 2013 7:35 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:09 PM
Points: 101, Visits: 246

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

Sean Lange

Posted Monday, January 14, 2013 8:03 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 8,567, Visits: 8,218

briancampbellmcad (1/14/2013)

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

Post #1406750

ScottPletcher

Posted Monday, January 14, 2013 12:33 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:37 PM
Points: 1,319, Visits: 1,766

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)
One man with courage makes a majority. Andrew Jackson

Post #1406910

« Prev Topic | Next Topic »

Permissions