Solved

SQL2005 Error due to trigger

Posted on 2011-03-21
8
614 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
 

Author Comment

by:jdr0606
ID: 35185557
"Company" is the database where "RM00101" is the customer master table i.e. Company.dbo.rm00101
0
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.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Rebuild index results in duplicate key violation 9 42
Sql Permission 6 52
Following an example - removing duplicate strings 4 50
SQL Restore Script - Syntax Error 8 91
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

12 Experts available now in Live!

Get 1:1 Help Now