May 2, 2002 at 1:29 pm
We have two linked SQL 7 servers (service pack 2). One has our accounting database and the other is our operational database. We are attempting to create a trigger that fires on our accounting inventory table whenever the average cost changes that will update the inventory table on our operational database.
The trigger syntax is very simple since we are testing:
--------------------------------------------------------
CREATE TRIGGER OSG_UpdateStandardAvgCost ON MMINV_UPDATE FOR UPDATE AS
DECLARE @ITEMNMBRvarchar(30),
@STNDCOSTmoney,
@CURRCOSTmoney
SELECT @ITEMNMBR = ITEMNMBR FROM inserted
SELECT @STNDCOST = STNDCOST FROM inserted
SELECT @CURRCOST = CURRCOST FROM inserted
/* UPDATE Operational database
- Standard to standard
- Average is Current cost */
--print 'Updating the Standard Cost and Average Cost'
update mm1.davesmasdb.dbo.item SET STD_COST = @STNDCOST where ITEM_NO = @ITEMNMBR
update mm1.davesmasdb.dbo.item SET AVG_COST = @CURRCOST where ITEM_NO = @ITEMNMBR
----------------------------------------------------------
However, whenever we update the costs in our accounting inventory table we get the following error message:
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE DB Provider returned message: Cannot start more transactions on this session.]
[Microsoft][ODBC SQL Server Driver][SQL Server][Could not start a transaction for OLE DB provider 'SQLOLEDB']
Would anyone know how to correct this to make the update trigger fire properly to the database on the other linked server?
If triggers are unstable, would anyone have a better solution? Thanks for any help.
Don Saluga
Vector Security
Database Manager
May 2, 2002 at 2:55 pm
Look at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306649 should answer your question.
Also the way you are doing yuo update would be better, especially if you do a multi row update if you do the following
update
mm1.davesmasdb.dbo.item
SET
STD_COST = STNDCOST,
AVG_COST = CURRCOST
FROM
inserted
WHERE
ITEM_NO = ITEMNMBR
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply