SQL Server Performance Forum – Threads Archive

A simple trigger that doesn’t 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 ON

I have done updates to other databases fom trigger
insert into mailmax.mailmax5.dbo.[users] (username,password,domainid,Firstname,LastName … Are you getting an error? or check the permission for the other database
—————————————-
http://spaces.msn.com/members/dineshasanka

You’re not anticipating multi-row updates and inserts. Since the trigger is on a table with Person as a FK, you need to update the Users table just once for each distinct Person code. UPDATE U
SET U.status = 1
FROM Intranet.dbo._Users U
WHERE U.personNo IN (SELECT inserted.Person FROM inserted)

or UPDATE U
SET U.status = 1
FROM Intranet.dbo._Users U
Inner Join inserted I on U.personNo =I.person
Madhivanan Failing to plan is Planning to fail

Hi,
I do not understand why there’s a need for SET U.status = 1. I have been trying to create a multiple row trigger but have been very unsuccessful. What I’m needed to do is to take information from Table_1 and update to Table_2 upon INSERT. However, the below query doesn’t work and causes my system to hang. Kindly advice where my query has gone wrong. CREATE TRIGGER TRG_UPD ON [dbo].[Table_1]
FOR INSERT
AS DECLARE @Vessel as nvarchar (30)
DECLARE @ItemSpec as nvarchar (30)
DECLARE @ETA as DateTime
DECLARE @ItemCode as nvarchar (20)
DECLARE @DocEntry as int
DECLARE @LineNum as int UPDATE Table_2
SET @Vessel = (SELECT i.U_PVesselL from Inserted i INNER JOIN Table_1 T1 ON i.ItemCode = T1.ItemCode AND i.DocEntry = T1.DocEntry AND i.LineNum = T1.LineNum)
SET @ItemSpec = (SELECT i.U_PItmSpec from Inserted i INNER JOIN Table_1 T1 ON i.ItemCode = T1.ItemCode AND i.DocEntry = T1.DocEntry AND i.LineNum = T1.LineNum)
SET @ETA = (SELECT i.U_ETA from Inserted i INNER JOIN Table_1 T1 ON i.ItemCode = T1.ItemCode AND i.DocEntry = T1.DocEntry AND i.LineNum = T1.LineNum)
SET @ItemCode = (SELECT i.ItemCode from Inserted i INNER JOIN Table_1 T1 ON i.ItemCode = T1.ItemCode AND i.DocEntry = T1.DocEntry AND i.LineNum = T1.LineNum)
SET @DocEntry = (SELECT i.DocEntry from Inserted i INNER JOIN Table_1 T1 ON i.ItemCode = T1.ItemCode AND i.DocEntry = T1.DocEntry AND i.LineNum = T1.LineNum)
SET @LineNum =(SELECT i.LineNum from Inserted i INNER JOIN Table_1 T1 ON i.ItemCode = T1.ItemCode AND i.DocEntry = T1.DocEntry AND i.LineNum = T1.LineNum) IF EXISTS (SELECT * FROM Table_2
WHERE ItemCode = @ItemCode AND BaseEntry = @DocEntry AND BaseLinNum = @LineNum)
BEGIN
INSERT INTO Table_2 (U_PVessel, U_PItmSpec, U_ETA)
SELECT @Vessel, @ItemSpec, @ETA
END
Thanks,
Ching Sean

Ching Sean, It looks like you don’t understand the UPDATE syntax too well. You’re trying to combine a lot of stuff within the UPDATE statement, but the syntax is not as flexible as you want it to be. Your statement - UPDATE Table_2 SET @Vessel = <……> - is void because the SET part must apply a value to a column, not to a variable. The UPDATE Table_2 statement doesn’t do anything. The SET statements after it do work: each one applies a single value to each of your variables. Which value is applied to each of the variables is only clear in case the trigger fires for an insert of a single row. If multiple rows are inserted then you will get errors, because the subqueries will be returning multiple rows. Next, you are trying to cover the issue whether to update a matching record in Table_2, or to insert a new one if it doesn’t exist. You have to check for matching record before anything else – like IF EXISTS (SELECT * FROM Table_2 INNER JOIN inserted ON Table_2.col1 = inserted.col1) BEGIN UPDATE Table_2 SET colX = inserted.colX, colY = inserted.colY FROM Table_2 INNER JOIN inserted ON Table_2.col1 = inserted.col1 END (Note the comma between columns in the SET part!) Then, as a second step you insert any unmatched rows – like this: IF EXISTS (SELECT * FROM inserted WHERE NOT EXISTS (SELECT * FROM Table_2 WHERE Table_2.col1 = inserted.col1)) BEGIN INSERT INTO Table_2 (<column_list&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> SELECT <column_list> FROM inserted LEFT JOIN Table_2 ON inserted.col1 = Table_2.col1 WHERE Table_2.col1 IS NULL WHERE END There is no need for variables to transfer the data, and there is no need to handle single/multiple rows separately. Even if half your rows do have a match, and the other half are new, then they will all be handled correctly. A final word: do not handle the unmatched rows before the matching rows. In that case, when you do the update, the new rows will have matching rows as well, and will be updated unnecessarily.

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

]]>