SQL Trigger not working on imported data

I have written a simple trigger that works fine on data that is inserted via SQL Server Enterprise Manager, but fails to work when importing data from an Excel spreadsheet.  Any thoughts would be appreciated.


Here is the trigger.  Any help would be appreciated.
CREATE TRIGGER [spUpdatePrograms] ON dbo.ProgramsQB 
FOR INSERT, UPDATE
AS
Declare @PatientID int, @ProgramFeesPD money, @ProgramExpdate smalldatetime, @datefeespd smalldatetime, @ProgramName varchar
 
Select @PatientID = PatientID from inserted
Select @ProgramFeesPD = ProgramFeesPD from inserted
Select @Datefeespd = DateFeesPd from inserted
Select @ProgramName = ProgramName from inserted
Update Patients
  set ProgramExpDate = @DateFeesPd + 365,  LastProgramAmt = @ProgramFeesPd, ProgramName=@ProgramName
 
  from inserted inner join Patients on inserted.patientid = patients.id

Open in new window

aetherlyAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Excellent.  Please go ahead and close this thread.
0
 
Anthony PerkinsCommented:
Triggers are fired on Insert statement not one per row.

Try it this way:

CREATE TRIGGER [spUpdatePrograms] ON dbo.ProgramsQB

FOR INSERT, UPDATE

AS

Update      Patients
Set            ProgramExpDate = i.DateFeesPd + 365,  
            LastProgramAmt = i.ProgramFeesPd,
            ProgramName = i.ProgramName
From      Patients p
            Inner Join inserted i on p.id = i.patientid
0
 
aetherlyAuthor Commented:
I tried this but I keep getting a syntax error when trying to save the trigger.  I had tried that approach before I switchd to the approach using variables.  I don't understand why it works on a single inserb, but not one a file import.  My understanding is that all of the same triggers should fire on an import that fire on a normal insert.  I originally tried to write this as a stored procedure but couldn't figure it out that way either.  I would also accept a solution using a stored procedure to accomplish the same end result.

Thanks.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Anthony PerkinsCommented:
>>I tried this but I keep getting a syntax error when trying to save the trigger.<<
Why don't you post the error message and the structure of the tables ProgramsQB and Patients?

>>I don't understand why it works on a single inserb, but not one a file import. <<
That would be because you have failed to use a staging table(s) and are resorting to inserting directly into Production table(s) without knowing that Triggers are not fired on bulk inserts (and they are not even logged)
0
 
Anthony PerkinsCommented:
P.S. If you are using DTS and still insist on inserting directly into a Production table, try disabling Fast Load in the Transformation options.
0
 
aetherlyAuthor Commented:
I tried doing this through a temporary table, but couldn't work it out.  Can you suggest how to do it with temporary tables?
0
 
Anthony PerkinsCommented:
If by temporary table you mean a staging table than do the following:
1. Create a table with the same structure as the Excel spreadsheet.
2. Import the file into that staging table.
3. Create an Execute SQL Task to insert the data into your production table using the staging table as a source.
0
 
aetherlyAuthor Commented:
Ok, I did fine with the first 2 pieces, but am not familiar with step 3.  Can you provide some ode to do the execute sql task?  By the way, I tried just using dts with the staging table and got the same result as with the excel spreadsheet.
0
 
Anthony PerkinsCommented:
Not until you post the structure of the tables involved.  In other words, I will need CREATE TABLE statements for your staging table and (as requested previously) ProgramsQB.
0
 
aetherlyAuthor Commented:
Staging table script
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Sheet1$](
      [Name] [nvarchar](255) NULL,
      [Account] [float] NULL,
      [Memo] [nvarchar](255) NULL,
      [Amount] [float] NULL,
      [Date] [datetime] NULL
) ON [PRIMARY]

GO
ProgramsQB Script
USE [PPMD]
GO

/****** Object:  Table [dbo].[ProgramsQB]    Script Date: 04/21/2009 12:01:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ProgramsQB](
      [PatientID] [nchar](10) NOT NULL,
      [ProgramName] [char](20) NOT NULL,
      [ProgramFeesPd] [numeric](18, 0) NOT NULL,
      [DateFeesPd] [datetime] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

In addition to the above posted trigger, there is an additional trigger on the Patients Table that does the following calculation (english version provided below)

Set ProgramExpDate = DateFeesPd (from ProgramsQB) + 360
If ProgramExpDate < getdate(), set ProgramExpired = 1

Thanks for your help.
0
 
Anthony PerkinsCommented:
Now please tell me how you map the staging table column values to the ProgramsQB table.
0
 
aetherlyAuthor Commented:
Sorry, I can see why you would need that.  Here is the mapping.

Sheet1$ Account = ProgramsQB PatientID
Sheet1$ Memo = ProgramsQB ProgramName
Sheet1$ Amount = ProgramsQB ProgramFeesPd
Sheet1$ Date = ProgramsQB DateFeesPd

Thanks for your help.
0
 
Anthony PerkinsCommented:
Than all you need is the following INSERT statement in the Execute SQL Task:

Insert      ProgramsQB (PatientID, ProgramName, ProgramFeesPd, DateFeesPd)
Select      Account,
            Memo,
            Amount,
            Date
From      [Sheet1$]
0
 
aetherlyAuthor Commented:
Ok, thanks for your help.  This gets me closer but when the trigger fires that moves the results to the patients table, I ended up with the same value for all of the updated records as the value for the first record.  In other words, they all ended up with the same DateFeesPd and the same expiration date.  Obviously this "easy" little issue that I thought I had is more complex.  I am going to get some real time paid help on this one.  I really appreciate your efforts to help me.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.