Solved

SQL2005 Error due to trigger

Posted on 2011-03-21
8
611 Views
Last Modified: 2012-05-11
I created a trigger to capture changes to a field containing a SalesTerritory description.

If I update the table with the trigger using SQL Management Studio it succeeds without any problems.

We are using Great Plains as our ERP and the table in question with the trigger is for the Great Plains customer master.  If we update the customer master via the Great Plains application it throws an error.
  Unhandled database exception:
  A save operation on table 'RM_Customer_MSTR' failed accessing SQL data.
  EXCEPTION_CLASS_DB
  DB_ERR_SQL_DATA_ACCESS_ERR

I realize that no one may be familiarr with Great Plains  but what may be causing this to ocurr?

One other issue that we've seen is that when we add a new customer record through Great Plains, no error is generated but the trigger does not create an entry into the udtAuditTerritory table.

I have attached the trigger to this message.

Any assistance is appreciated!
insert into dynamics_ext.dbo.udtAuditTerritory 
		select d.custnmbr
			, d.salsterr --'From'
			, i.salsterr --'To'
			, getdate()
			, (system_user)login_Name
			, (user)user_name
			, (@@spid)spid
			, (host_name())hostname
		from deleted d join inserted i 
		on d.custnmbr = i.custnmbr 
			and d.salsterr <> i.salsterr

Open in new window

0
Comment
Question by:jdr0606
  • 4
  • 4
8 Comments
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
That's the SQL statement from the trigger . . . but not the entire trigger creation statement.  can you provide the entire trigger creation statement?  (E.g. CREATE TRIGGER with the ON INSERT or ON UPDATE)
0
 

Author Comment

by:jdr0606
Comment Utility
Sorry!

Complete Trigger attached!

Thanks
USE [COMPANY]
GO
/****** Object:  Trigger [dbo].[utrAuditTerritory]    Script Date: 03/21/2011 18:32:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[utrAuditTerritory]
   ON  [COMPANY].[dbo].[RM00101]
   AFTER UPDATE
AS 
BEGIN
      SET NOCOUNT ON;
      insert into dynamics_ext.dbo.udtAuditTerritory 
		select d.custnmbr
			, d.salsterr --'From'
			, i.salsterr --'To'
			, getdate()
			, (system_user)login_Name
			, (user)user_name
			, (@@spid)spid
			, (host_name())hostname
		from deleted d join inserted i 
		on d.custnmbr = i.custnmbr 
			and d.salsterr <> i.salsterr
END

Open in new window

0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
Well, I can quickly identify why you don't get an entry when you add a company . . . this is an UPDATE trigger, which means it only fires on UPDATES.  To get it to fire when you do the INSERT of the new customer, you would need to have an AFTER INSERT trigger (they can be combined but my preference is to keep them separate because, otherwise, the combined one can get more complicated).

as for the problem when you update the customer master table . . . I am a little puzzled by the fact that you have provided a trigger from the Company table.  Is the RM00101 table the same as the "customer master"?  Your initial post indicates that you would have been updating the RM_Customer_MASTER?!?!?!
0
 

Author Comment

by:jdr0606
Comment Utility
"Company" is the database where "RM00101" is the customer master table i.e. Company.dbo.rm00101
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
Okay, the RM00101 is synonymous with RM_Customer_MASTER . . . I can accept that. ;-)

However, the fact remains that your trigger will only fire on an UPDATE and will NOT fire for an INSERT.  If you want the same trigger to fire after an INSERT, just add INSERT, before the word UPDATE in line 10 of the code that jdr0606 provided in Response #35185431.
0
 

Author Comment

by:jdr0606
Comment Utility
OK here is where I'm at.

I found the reason for the error and have corrected.  (The udtAuditTerritory table required application permissions in order to write to the table and was erroring because it didn't).

I understand that I need an additional trigger to handle new Customer records which I will create, however now that the permissions have been addressed I don't get an error in the application when I update a Customer Master record but the update trigger doesn't create an entry in the udtAuditTerritory table.

0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 500 total points
Comment Utility
Try the attached slight modification to the UPDATE Trigger.  

NOTE: I highly recommend always listing the columns into which data is being inserted rather than depending on the default of just saying INSERT INTO tablename.
USE [COMPANY]
GO
/****** Object:  Trigger [dbo].[utrAuditTerritory]    Script Date: 03/21/2011 18:32:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[utrAuditTerritory] 
ON [COMPANY].[dbo].[RM00101] 
AFTER UPDATE 
AS 
BEGIN
	SET NOCOUNT ON;
	IF UPDATE(salsterr)
		INSERT INTO dynamics_ext.dbo.udtAuditTerritory
		(
			 custnmbr
			,{list the rest of the columns to be inserted into}
		)
		SELECT d.custnmbr
			  ,d.salsterr --'From'
			  ,i.salsterr --'To'
			  ,GETDATE()
			  ,(system_user) login_Name
			  ,(USER) user_name
			  ,(@@spid) spid
			  ,(host_name()) hostname
		FROM Deleted d
		INNER JOIN Inserted I
		ON I.custnmbr = d.custnmbr
END

Open in new window

0
 

Author Comment

by:jdr0606
Comment Utility
Perfect, it is working as needed.

I was stupid, when I said the update wasn't working I forgot that the trigger would only add to the Audit table when the Sales Territory changed and ignore all others.

With that said I don't seem to be able to make an Insert trigger work.

Suggestions?

Thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now