Insert Trigger on SOP10106 table giving errors

(0) Share

Report

Posted on by Community Member Microsoft Employee

I wrote a trigger on the SOP10106 table for update delete. It works perfectly.

Now the requirement is for insert also. I went in the modified the trigger to insert,update,delete and it keeps giving me errors in GP.

As soon I change it to update,delete, it works fine.

Am I missing something here?

*This post is locked for comments

All responses (3)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

Re: Re: Re: Insert Trigger on SOP10106 table giving errors

Your problem is getting the information for the @sopnmbr and @Soptype from deleted as there is nothing in deleted when the insert trigger runs. You might do better setting up a separate Insert Trigger or coding to get the information from the inserted table depending on counting the records in deleted first Paul
Community Member Microsoft Employee on at

Like (0)

Report

Re: Re: Insert Trigger on SOP10106 table giving errors

The trigger is on the sop10106 table. Sop10106 table stores extra values for Sales order processing entry. The trigger writes data to a custom table when USRDEF04 field is modified. The code for the trigger is below:

USE [MCN]

GO

/****** Object: Trigger [dbo].[_cb_MFGHRS] Script Date: 09/16/2010 15:14:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER trigger [dbo].[_cb_MFGHRS] on [dbo].[SOP10106]
for insert, update,delete as
declare @MFGHRSOld varchar(50)

declare @MFGHRSNew varchar(50)

declare @SOPNmbr varchar(50)
declare @SOPTYPE int
set @mfghrsold = (select usrdef04 from deleted )

set @mfghrsnew = (select usrdef04 from inserted )

set @Sopnmbr = (select sopnumbe from deleted)

set @Soptype = (select soptype from deleted)

if update(usrdef04)

Begin
if @mfghrsold <> @mfghrsnew
begin
insert _cb_SOP10106_Audit values
( @SopNmbr, @Soptype, system_user, getdate(), @mfghrsold, @mfghrsnew, 'Update')
end
end

if @mfghrsnew is null

begin
insert _cb_SOP10106_Audit values
( @SopNmbr, @Soptype, system_user, getdate(), @mfghrsold, @mfghrsold, 'Delete')

end

if @mfghrsold is null

begin
insert _cb_SOP10106_Audit values
( @SopNmbr, @Soptype, system_user, getdate(), @mfghrsold, @mfghrsold, 'New')

end
Bron 4,887 on at

Like (0)

Report

Re: Insert Trigger on SOP10106 table giving errors

Refresh my memory - what does the sop10106 store?

And what exactly is your trigger trying to accomplish is it on an external table and trying to modify sop10106 or is it on the sop10106 and trying to..............do what - external table or another GP table?

Posting the code for the trigger might also be helpful.......

Insert Trigger on SOP10106 table giving errors

Helpful resources

News and Announcements

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Community AMA December 12th

Leaderboard

Featured topics

Product updates

Insert Trigger on SOP10106 table giving errors

Helpful resources

News and Announcements

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Community AMA December 12th

Subscribe to

Leaderboard

Featured topics

Product updates