?
Solved

SQL Trigger not working on imported data

Posted on 2009-04-19
14
Medium Priority
?
683 Views
Last Modified: 2013-11-30
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

0
Comment
Question by:aetherly
  • 8
  • 6
14 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24181731
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
 

Author Comment

by:aetherly
ID: 24181769
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24181807
>>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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24181814
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
 

Author Comment

by:aetherly
ID: 24181826
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24183884
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
 

Author Comment

by:aetherly
ID: 24184440
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24186642
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
 

Author Comment

by:aetherly
ID: 24197867
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24199966
Now please tell me how you map the staging table column values to the ProgramsQB table.
0
 

Author Comment

by:aetherly
ID: 24200619
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24200653
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
 

Author Comment

by:aetherly
ID: 24221377
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24224636
Excellent.  Please go ahead and close this thread.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question