SQL Trigger not working on imported data

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

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.

>>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)

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.

I tried doing this through a temporary table, but couldn't work it out.  Can you suggest how to do it with temporary tables?

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.

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.

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.

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.

Now please tell me how you map the staging table column values to the ProgramsQB table.

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.

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$]

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.