A simple trigger that doesn't work

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

A simple trigger that doesn't work

Author

Topic

francki17
Starting Member

4 Posts

Posted - 2006-01-18 : 16:36:45

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-18 : 16:38:26

Is the other database on a different server? If so, then you'll need to add a linked server to it. Then you'll reference it in your trigger.

LinkedServerName.Intranet.dbo._Users

Tara Kizer
aka tduggan

francki17
Starting Member

4 Posts

Posted - 2006-01-18 : 16:39:34

Hi Tara, no it's on the same server...

Francois

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-18 : 16:56:12

Are you getting an error when the trigger fires?

BTW, you have a fundamental problem with your trigger. You are assuming that only one row will be in the inserted table, when it could be more. See my blog entry for more information:

http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

Tara Kizer
aka tduggan

francki17
Starting Member

4 Posts

Posted - 2006-01-23 : 10:12:45

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources