two almost identical triggers working fine except one line in each

Question

two almost identical triggers working fine except one line in each

DaveBriCam

SSCarpal Tunnel

Points: 4294
More actions
November 20, 2012 at 11:46 am

#265136

I have two triggers that fire upon an insert that fires both simultaneously with no problems except this one line of each (ALONE they fire fine with this line)... clues?:
INSERTED.PO_Number + INSERTED.AppUser + INSERTED.SoftwareShortName + INSERTED.HeatTicketNumber,
Here are both triggers:
GO
USE [TrackIT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trPopulateJournalEntriesCredits]
ON [dbo].[tblTransactions]
AFTER INSERT
AS
BEGIN
INSERT INTO tblSWJournal
(tblSWJournal.Description,
tblSWJournal.Amt,
tblSWJournal.Cost_Center)
SELECT
INSERTED.PO_Number + INSERTED.AppUser + INSERTED.SoftwareShortName + INSERTED.HeatTicketNumber,
(INSERTED.Unit_Price * -1),
'823008'
FROM INSERTED
WHERE INSERTED.Transaction_Type = 'From Bulk Assignment'
END
GO
USE [TrackIT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trPopulateJournalEntries]
ON [dbo].[tblTransactions]
AFTER INSERT
AS
BEGIN
INSERT INTO tblSWJournal
(tblSWJournal.Description,
tblSWJournal.Amt,
tblSWJournal.Cost_Center)
SELECT
INSERTED.PO_Number + INSERTED.AppUser + INSERTED.SoftwareShortName + INSERTED.HeatTicketNumber,
INSERTED.Unit_Price,
INSERTED.AllocationCostCenter
FROM INSERTED
WHERE INSERTED.Transaction_Type = 'From Bulk Assignment'
END

Viewing 9 posts - 1 through 8 (of 8 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I guess it would help to know what the problem is. You say they work "just fine" alone, but "have a problem" when fired in tandem, but you don't say what the problem is.

Do you get an error message? If so, what?

Does it insert something odd in your tblSWJournal table? If so, what?

Do they end up doing nothing at all?

Something else?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323504 More actions · Answer 2

i see that if any of these four fields are null, the value inserted would be null:

INSERTED.PO_Number + INSERTED.AppUser + INSERTED.SoftwareShortName + INSERTED.HeatTicketNumber,

also , depending on the datatypes, if any of them are integers(PONumber/Ticketnumber?), the trigger would fail due to cannot convert varchar to integer type errors.

I'd have expected that a seperator would be used between the values as well, so you can easily tell where one starts and another begins...

also how big is the destination fields tblSWJournal.Description? is it big enough for the max length of all four concatenated fields?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

Since nobody else has asked...couldn't these two triggers be combined into a single trigger with 1 insert statement?

SELECT INSERTED.PO_Number + INSERTED.AppUser + INSERTED.SoftwareShortName + INSERTED.HeatTicketNumber

,(INSERTED.Unit_Price * - 1)

,'823008'

FROM INSERTED

WHERE INSERTED.Transaction_Type = 'From Bulk Assignment'

UNION ALL

SELECT INSERTED.PO_Number + INSERTED.AppUser + INSERTED.SoftwareShortName + INSERTED.HeatTicketNumber

,INSERTED.Unit_Price

,INSERTED.AllocationCostCenter

FROM INSERTED

WHERE INSERTED.Transaction_Type = 'From Bulk Assignment'

This seems a LOT simpler to me with the same result.

_______________________________________________________________

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/

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 4

I had originally hoped to do just that with one trigger... but the trigger needs to create two records. I put the complete SQL below in and actually get THREE records... two of the FIRST block:

USE [TrackIT]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[trPopulateJournalEntries]

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.Case_Number,

tblSWJournal.Product,

tblSWJournal.BU,

tblSWJournal.Reins_Code,

tblSWJournal.Dist_Chan,

tblSWJournal.Fund,

tblSWJournal.State,

tblSWJournal.Process,

tblSWJournal.Affiliate,

tblSWJournal.Rt_Type,

tblSWJournal.Rate,

tblSWJournal.Base_Amt,

tblSWJournal.Stat_Amt,

tblSWJournal.Scenario,

tblSWJournal.Open_Item_Key,

tblSWJournal.Policy_Number,

tblSWJournal.Certificate_Number,

tblSWJournal.Agent_PID,

tblSWJournal.Investment_Asset_ID,

tblSWJournal.Check_Number,

tblSWJournal.Cash_Effective_Date,

tblSWJournal.Fund_Sponsor,

tblSWJournal.Suspense_Control_Number,

tblSWJournal.Ariba_Unique_Number,

tblSWJournal.QualifiedNonQualified,

tblSWJournal.Claim_#)

SELECT

INSERTED.PO_Number + INSERTED.AppUser + INSERTED.SoftwareShortName + INSERTED.HeatTicketNumber,

(INSERTED.Unit_Price * -1),

'823008',

'ACTUALS',

'C',

'USD',

'153355930',

'',

''

FROM INSERTED

WHERE INSERTED.Transaction_Type = 'From Bulk Assignment'

UNION ALL

SELECT

INSERTED.PO_Number + INSERTED.AppUser + INSERTED.SoftwareShortName + INSERTED.HeatTicketNumber,

INSERTED.Unit_Price,

INSERTED.AllocationCostCenter,

'ACTUALS',

'C',

'USD',

'60011060',

'',

''

FROM INSERTED

WHERE INSERTED.Transaction_Type = 'From Bulk Assignment'

END

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 5

None of these have a possibility of being NULL: INSERTED.PO_Number + INSERTED.AppUser + INSERTED.SoftwareShortName + INSERTED.HeatTicketNumber,

None of the values are integers...

I'd have expected that a seperator would be used between the values as well, so you can easily tell where one starts and another begins... - the business requirements of another component dictate a continuous string

also how big is the destination fields tblSWJournal.Description? is it big enough for the max length of all four concatenated fields? - I checked to make sure that the destination field could hold any possible field length of the four combined

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

I put the complete SQL below in and actually get THREE records... two of the FIRST block:

Looking at your code I don't quite see how that is possible. How many rows did you insert when you tested it? I of course can't test it because we don't have ddl for the base table. It sure looks like something else is going on.

_______________________________________________________________

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/

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 7

Thanks... your idea worked of thr UNION All and one trigger!.... my misstake was I has not deleted my original query which was still firing producing that dupe record.... many thanks!

Sean Lange SSC Guru Points: 286573 More actions · Answer 8

briancampbellmcad (11/21/2012)
Thanks... your idea worked of thr UNION All and one trigger!.... my misstake was I has not deleted my original query which was still firing producing that dupe record.... many thanks!

You're welcome. Glad that worked for you.

_______________________________________________________________

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/