Access ADP and SQL Server trigger

[VUILLERMET Jacques]

An Access ADP client works fine with a SQL Server 2000 database.

The client has a form to one table.

When I add a trigger (INSERT, UPDATE) on this table (the trigger modifies
the table, source code after signature) then Access is lost : error message
translated from French "Writing conflict - This record has been modified by
another user ... [Save record] [Copy to the Clipboard] [Cancel]".

The trigger works fine with another client, Query Analyser for example.

Is it possible to use Access (ADP) with triggers ?

Jacques.

***********************************************
CREATE TRIGGER trg_AvoidNullInNb ON [dbo].[MvtColis]
FOR INSERT, UPDATE
AS

UPDATE Mvtcolis
SET NbPal = 0
WHERE NbPal is null

UPDATE Mvtcolis
SET NbColis = 0
WHERE NbColis is null

UPDATE Mvtcolis
SET NbPlis = 0
WHERE NbPlis is null

UPDATE Mvtcolis
SET NbPiece = 0
WHERE NbPiece is null

/* Fin.*/

 

[Vadim Rapp]

VJ> Is it possible to use Access (ADP) with triggers ?

VJ> UPDATE Mvtcolis
VJ> SET NbPal = 0
VJ> WHERE NbPal is null

Your trigger is modifying not only the inserted row, but all rows in the
table, so the message Access is showing is correct. What you want is

UPDATE Mvtcolis
SET NbPal = 0
from mvtcolis inner join inserted on inserted.<keycolumn> =
mvtcolis.<keycolumn>
WHERE NbPal is null

In this particular case, however, instead of the trigger you can simply
define default value=0 for the columns nbPal etc.

Vadim

 

[VUILLERMET Jacques]

Vadim, thank you for your answer.

Your trigger is modifying not only the inserted row,

Yes. Lot of people notice this point (even if the error message says "This
record").
So I've tried, but the result is the same with :

UPDATE Mvtcolis
SET NbPal = 0
WHERE NbPal is null
AND id = (SELECT id FROM inserted)
/*! only one update at a time ! */

or with :

UPDATE Mvtcolis
SET NbPal = 0
FROM MvtColis INNER JOIN inserted
ON MvtColis.id = inserted.id
WHERE inserted.NbPal is null

In this particular case, however, instead of the trigger you can simply

define default value=0 for the columns nbPal etc.
On insert or update, the application always specifies a value : the default
value property is not applicable (...and already applied ;-) ;

What I want : if a Null value is specified by the application, then I want
to replace it by 0.

[Access 2000 - SQL Server 2000 SP3]

Thank's again.

Jacques.

 

[Uwe Ricken]

Hi Jacques,

Vadiim has told you the mistake you've made in your trigger.
On the other hand it is useful als Vadim has written, to
use DEFAULT-Values.

If you don't want to use DEFAULTS you do not have to use
multiple UPDATE-Statements within the trigger but you can
handle it within only one UPDATE-Statement

CREATE TRIGGER dbo.trg_AvoidNullInNb
ON dbo.MvtColis
FOR INSERT, UPDATE
AS
SET NOCOUNT ON

UPDATE m
SET m.nbPal = ISNULL(i..NbPal, 0),
m.nbColIs = ISNULL(i.nbColIs, 0),
m.nbPlis = ISNULL(i.nbPlis, 0),
m.NBPiece = ISNULL(i.NBPiece, 0)
FROM dbo.Mvtcolis m INNER JOIN inserted i
ON (m.<PKField> = i.<PKField>)

SET NOCOUNT OFF
GO

HTH ;-)

--
Gruß, Uwe Ricken
MCP for SQL Server 2000 Database Implementation

GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
____________________________________________________
APP: http://www.AccessProfiPool.de
dbdev: http://www.dbdev.org
FAQ: http://www.donkarl.com/AccessFAQ.htm

 

[VUILLERMET Jacques]

It works with one UPDATE as you've shown !

THANKS A LOT !!!

Jacques. ))

 

[Vadim Rapp]

VJ> So I've tried, but the result is the same with :

VJ> UPDATE Mvtcolis
VJ> SET NbPal = 0
VJ> WHERE NbPal is null
VJ> AND id = (SELECT id FROM inserted)
VJ> /*! only one update at a time ! */

put SET NOCOUNT ON before the updates.


Vadim

 

[VUILLERMET Jacques]

Ok !

Thank you Vadim !