want trigger to fire when Sales Entry batch is posted

[ctwilliams]

I am trying to write a trigger that will fire whenever a Sales Entry batch is posted. The trigger will execute a stored procedure, which will perform some tasks in the SOP30200 table to the invoices/returns that were posted.

However, I am having trouble determining what conditions to make the trigger fire on. I have run some SQL Profiler traces and it appears that when a batch is posted, it is sometimes deleted from the SY00500 table, but other times the BCHSTTUS and MKDTOPST fields are updated and the record is left in the table. The record appears to always be deleted from the SY00800 table in DYNAMICS, but I am afraid to put a trigger on this table since it affects so many other processes.

Here is my trigger:

CREATE TRIGGER [MyTrigger] ON SY00500 FOR DELETE AS
IF EXISTS (SELECT * FROM deleted WHERE BCHSOURC = 'Sales Entry')
BEGIN
	DECLARE @BACHNUMB varchar(15)
	SELECT @BACHNUMB = BACHNUMB FROM deleted
	EXEC MyStoredProc @BACHNUMB
END

Here is the code (obtained from Profiler) that is being executed to delete the batch record from the SY00800 table:

DELETE FROM DYNAMICS.dbo.SY00800
WHERE WINTYPE = 2 AND USERID = 'XXXX' 
AND CMPNYNAM = 'XXXX' AND BCHSOURC = 'Sales Entry' 
AND BACHNUMB = 'XXXX' 
and (not exists (select 1 from tempdb.dbo.DEX_LOCK where row_id = 1196696 and table_path_name = 'DYNAMICS.dbo.SY00800')

 

[winthropdc]

Do you have access to a Dexterity developer?

It is fairly easy to create a trigger against the posting procedure and use it to call your stored procedure.

David Musgrave
Senior Development Consultant
MBS Services - Asia Pacific

Microsoft Business Solutions

http://www.microsoft.com/BusinessSolutions

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.

 

[ctwilliams]

I don't have access to a Dexterity developer. However, I think I figured out a way to trigger it in SQL...

CREATE TRIGGER [tr_DocFormUpdate] ON [dbo].[SY00500] FOR UPDATE AS

IF EXISTS (SELECT * FROM inserted WHERE BCHSOURC = 'Sales Entry')
BEGIN	
	DECLARE @BCHSTTUSold smallint, @MKDTOPSTold tinyint
	SELECT @BCHSTTUSold=BCHSTTUS, @MKDTOPSTold=MKDTOPST FROM deleted
	
	DECLARE @BACHNUMB varchar(15), @BCHSTTUS smallint, @MKDTOPST tinyint
	SELECT @BACHNUMB=BACHNUMB, @BCHSTTUS=BCHSTTUS, @MKDTOPST=MKDTOPST FROM inserted

	IF @MKDTOPSTold = 1 AND @MKDTOPST = 0 AND @BCHSTTUS = 0
	BEGIN	
		EXEC MyStoredProc @BACHNUMB
	END
END