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
Solved

SQL2005 Error due to trigger

Posted on 2011-03-21
8
618 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
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

829 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