January 18, 2006 at 2:30 pm
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 ON
January 18, 2006 at 2:48 pm
What if you UPDATE more than 1 row at once ?
>>SELECT @noPerson = Person FROM INSERTED
What are you expecting to be in @noPerson if there are multiple records in the INSERTED table ? This trigger can't handle multi-row inserts & updates.
January 18, 2006 at 2:54 pm
Hi,
at the begginn i wrote SELECT TOP 1 @noPerson = Person FRO INSERTED, but I removed it (I thought that it could be the reason why it doesn't work).
January 18, 2006 at 2:58 pm
Get rid of the variable and use a true set-based update:
IF UPDATE(LastMove)
BEGIN
UPDATE u
SET status = 1
FROM Intranet.dbo._Users AS u
INNER JOIN INSERTED AS i
ON (u.personNo = i.Person)
END
January 19, 2006 at 1:59 am
Hi,
I was wondering if this is a security issue. I looked in BOL but couldn't find any information regarding the security context in which a trigger operates, so perhaps I must assume it uses the context of the user who performed the operation which fired the trigger
If this is true, the data you are updating is in another database. when you test the trigger (logged in as who?) it works, but it doesn't work 'normally' when you perform an update or insert from your normal environment.
Does the login account you use in the 'normal' situation have appropriate access to the other database?
Do you get an error message of any sort?
David
If it ain't broke, don't fix it...
January 19, 2006 at 6:44 am
What does not work? What is the error? Is it that you cannot start a distributed transaction? (I am assuming based on the name of the target server that you are attempting to update a database outside the firewall.)
January 19, 2006 at 12:03 pm
>>I am assuming based on the name of the target server
It is not a "target server". It is simple 3-part naming of a table - "Intranet" in this case is the name of a database on the same SQL instance.
January 23, 2006 at 8:13 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply