Trigger on Insert in table..Udate field (multiple) other table. 1

[chiplarsen]

I have a trigger that I created that I want to update my Billing_Table.DISCHARGE_DT with a value that was inserted into my DISCHARGE_TABLE. The date in the DISCHARGE_TABLE is inserted via a Bulk Insert from a txt file. The trigger parses fine, but it does not update the Billing_Table.DISCHARGE_DT field. There could be many fields that the trigger would update based on patient account numbers in each table. Here is the trigger.

USE [IPS_PROD]
GO
/****** Object:  Trigger [dbo].[TR_DISCHARGE_INSERT]    Script Date: 10/21/2009 19:59:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_DISCHARGE_INSERT] ON [dbo].[DISCHARGE_TABLE] AFTER INSERT AS 
BEGIN   
UPDATE BILLING_TABLE   SET BILLING_TABLE.DISCHARGE_DT = I.DISCHARGE_DT  FROM  Inserted I  
INNER JOIN BILLING_TABLE ON I.PAT_ACCT_NBR = BILLING_TABLE.PAT_ACCT_NBR 
END

Does anyone see anything that might be causing this? I also tried UPDATE in my trigger instead of INSERT. I am using MS SQL 2005. Thank you

 

[chiplarsen]

What I want this trigger to do is look in the BILLING_TABLE and anywhere that the DISCHARGE_TABLE.PAT_ACCT_NBR = the BILLING_TABLE.PAT_ACCT_NBR...I want it to update the BILLING_TABLE.DISCHARGE_DT with the DISCHARGE_TABLE.DISCHARGE_DT. Can I do something like that?

 

[chiplarsen]

Does anyone have time to look at this? Did I explain it ok? Thank you

 

[gmmastros]

There is a FIRE_TRIGGERS option in the Bulk Insert command.

FIRE_TRIGGERS
Specifies that any insert triggers defined on the destination table execute during the bulk-import operation. If triggers are defined for INSERT operations on the target table, they are fired for every completed batch.

[!]If FIRE_TRIGGERS is not specified, no insert triggers execute. [/!]

For more information, see Controlling Trigger Execution When Bulk Importing Data.

By default, triggers are disabled for bcp and Bulk Insert. There are ways to change the default.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I forgot to mention that your trigger looks ok to me. The problem is likely to be that your trigger is not getting fired/executed based on your Bulk Insert command.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chiplarsen]

Thank you gmmastros...I am going to go read.

 

[gmmastros]

can you show the command you use to bulk insert your data?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chiplarsen]

This is the bulk insert command. There might be another way to do this. I am self taught and have a lot more to learn.

bulk insert DISCHARGE_TABLE from 'c:\ftpfiles\ftpdsg\dsg_ips.txt'

 

[gmmastros]

try this:

bulk insert DISCHARGE_TABLE 
from 'c:\ftpfiles\ftpdsg\dsg_ips.txt'
with (FIRE_TRIGGERS)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chiplarsen]

George, I made the change to the bulk insert, but it is still not updating the billing table. Is there a way that I can make sure the trigger is firing? I thought I saw something about a print command in another trigger. I will go look. Thank you

 

[gmmastros]

Chip,

I just ran a quick test and it worked exactly like I expected it to.

Without the FIRE_TRIGGERS, the trigger did not run. With it, the trigger worked exactly like I expected it to.

This means.... we are probably dealing with data issues.

For now, I would recommend you add some debugging statements to the trigger. Something like this:

ALTER TRIGGER [dbo].[TR_DISCHARGE_INSERT] ON [dbo].[DISCHARGE_TABLE] AFTER INSERT AS
BEGIN   
	SET NOCOUNT ON

	Declare @InsertedCount Int

	Select @InsertedCount = Count(*) From Inserted

	Print 'Rows Inserted ' + Convert(VarChar(20), @InsertedCount) + ' rows'

	UPDATE	BILLING_TABLE   
	SET		BILLING_TABLE.DISCHARGE_DT = I.DISCHARGE_DT  
	FROM	Inserted I  
			INNER JOIN BILLING_TABLE 
				ON I.PAT_ACCT_NBR = BILLING_TABLE.PAT_ACCT_NBR

	Print 'Trigger updated ' + Convert(VarChar(20), @@RowCount) + ' rows'

END

Now, when you run your bulk insert statement, you should see (in the messages window) how many rows were inserted, and also how many rows in the billing table were updated.

Once you finally fix this issue, make sure you remove the debug code.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

One more thing... can you run this and post the output here.

Select Table_Name, Data_Type
From   Information_Schema.Columns
Where  Column_Name = 'PAT_ACCT_NBR'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chiplarsen]

George, I really do appreciate your help. Thank you

 

[chiplarsen]

This is the results of the query.

IPS_DSCHRG_DX varchar
BILLING_TABLE nvarchar
CURRENT_CENSUS nvarchar
DISCHARGE_TABLE nvarchar

It does look like the query is firing, but no updates. I am also learning about creating the proper keys in my tables. In the BILLING_TABLE, the PK is a combination of the PAT_ACCT_NBR and the CENSUS_DT. I could not make the PK just the PAT_ACCT_NBR because there will be multiple records during the patient's stay. The PK in the DISCHARGE_TABLE, is the PAT_ACCT_NBR. The PAT_ACCT_NBR will be unique in this table. Thanks again George, you are teaching me many things.

 

[chiplarsen]

George, it is working now. I think that it would have worked after you told me about FIRE_TRIGGERS command in the bulk insert. Thank you George.

 

[gmmastros]

It does look like the query is firing, but no updates.

Further proof that this is probably a data issue. If your PAT_ACCT_NBR column was an int (or something similar) I would probably not suspect a data issue. It sounds to me like there just aren't any matches on the join condition (the PAT_ACCT_NBR). I encourage you to take a good look at the data to determine what's going on. If you see a couple rows that appear to be the same, but are not joining (for the update), then you could convert the PAT_ACCT_NBR to varbinary to see what's really going on. For example, if one of the tables had a PAT_ACCT_NBR = 'xyz' and another had 'xyz<tab character>' then when you look at the data, you wouldn't see the tab character at all. But.... converting to varbinary will show it to you.

I hope this makes sense. Let me know if you want further clarification regarding my thoughts here.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

in that case... ignore my last comment. I'm glad it's working for you now.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chiplarsen]

I am embarrased to say you were right. There were no matches on PAT_ACCT_NBR. I should not have assumed that there were. I guess that is a lesson learned. Thank you