Solved

SQL2005 Error due to trigger

Posted on 2011-03-21
8
630 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35185421
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
ID: 35185431
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
ID: 35185542
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:jdr0606
ID: 35185557
"Company" is the database where "RM00101" is the customer master table i.e. Company.dbo.rm00101
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35191222
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
ID: 35191350
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
ID: 35191665
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
ID: 35194622
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

Industry Leaders: 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!

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 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