A simple trigger that doesnt work

Hi all, I have a problem with this trigger. It seams to be very simple, but it doesn't work...

I created a trigger on a table and I would want that this one updates a field of a table on a diffrent DB (Intranet). When I test it normally (a real situation), it doesn't work, but when I do an explicit update ("UPDATE AccesCard SET LastMove = getDate();" by example) it works.

If anyone could help me, I would appreciate.

NB: Is there a special way, in a trigger, to update a table when the table to update is on another BD ?

Francois

This is the trigger:
------------------

ALTER TRIGGER UStatus
ON AccesCard
AFTER UPDATE, INSERT
AS

DECLARE @.noPerson int

SET NOCOUNT OFF

IF UPDATE(LastMove)
BEGIN
SELECT @.noPerson = Person FROM INSERTED
UPDATE Intranet.dbo._Users SET Intranet.dbo._Users.status = 1 WHERE personNo = @.noPerson;
END

SET NOCOUNT ONyou are making the assumption that you are only updating 1 record at a time. triggers need to handle sets. think in joins.|||You would need to set up a linked server connection to do this. And DON'T DO IT! This is one of the worst things you can do with a trigger.|||--Use the following code. You update should handle multiple rows simultaneously. However, in case you DB exist on a different server, set up linked server first:

ALTER TRIGGER UStatus
ON AccesCard
AFTER UPDATE, INSERT
AS

SET NOCOUNT OFF

IF UPDATE(LastMove)
UPDATE A
SET A.status = 1
from Intranet.dbo._Users A, Inserted I
WHERE A.personNo = I.Person

SET NOCOUNT ON

--Scalability Experts.|||Hi all, first of all, thanks for your helpful advices, I greatly appreciate it. The trigger is now working properly.

How did I solve the problem:
As a lot of people suggested me to do, I changed some lines in the trigger to make it compatible with multiple insert or update statements and change the security contex. I added specifics users from the source DB on the target source.

Thanks all for your help ! Francois|||Perhaps the reason they are called "triggers" is because they make it so easy to shoot yourself in the foot...as you seem bound and determined to do after SQLDBA_2005 so kindly loaded the gun and showed you where to point it.

Just my opinion. :rolleyes: