Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 638
  • Last Modified:

SQL2005 Error due to trigger

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
jdr0606
Asked:
jdr0606
  • 4
  • 4
1 Solution
 
8080_DiverCommented:
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
 
jdr0606Author Commented:
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
 
8080_DiverCommented:
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
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!

 
jdr0606Author Commented:
"Company" is the database where "RM00101" is the customer master table i.e. Company.dbo.rm00101
0
 
8080_DiverCommented:
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
 
jdr0606Author Commented:
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
 
8080_DiverCommented:
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
 
jdr0606Author Commented:
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now