I have two similar triggers one on solodata table and one on components table.
I cannot get the trigger on solodata to execute. I have tried it coded
similar to waht is on the components table ad as I have pasted it below.
Below SDLineNo trigger 
I have pasted the one on components that does run. i checked and extn5 does
change, and the groupid in solodata is 66, I would appreciate any help on why
this is not running.


CREATE TRIGGER [SDLineNo] ON [Solodata]
FOR INSERT, UPDATE
AS

IF UPDATE (ExtN5)
BEGIN

DECLARE @SDL INT
SELECT @SDL = SOLODATALINK FROM INSERTED

UPDATE SOLODATA
SET IsoText = ReportedLineNo
FROM Components CP inner join Solodata SD ON CP.solodatalink =
sd.solodatalink
inner join Commondata CD ON
CP.commondatalink = cd.commondatalink
WHERE sd.GroupID IN (1,6,17,28,51,52,53,55,62,66,67,68) AND sd.ExtN5=1
and SD.solodatalink = @SDL
END

**********THIS IS THE ONE THAT WORKS
CREATE TRIGGER [IsoTextLineNo] ON [Components]
FOR INSERT, UPDATE
AS

IF UPDATE(COMMONDATALINK)
BEGIN

UPDATE SOLODATA
set ISOTEXT =
REPORTEDLINENO + '$VENT/DRAIN/INST$/MD-110 DEG$SEE ' + COMPONENTDESCRIPTION
FROM inserted i inner join commondata cd on i.commondatalink =
cd.commondatalink, solodata, components
where solodata.groupid = 69
and solodata.SoloDataLink = Components.solodatalink
and Components.CommonDataLink = CD.CommonDataLink

--need to have the code below put on solodata table also for change in extn5
UPDATE SOLODATA
set ISOTEXT =
REPORTEDLINENO
FROM inserted i inner join commondata cd on i.commondatalink =
cd.commondatalink, solodata, components
where Solodata.GroupID IN (1,6,17,28,51,52,53,55,62,66,67,68) AND
Solodata.ExtN5=1
and solodata.SoloDataLink = Components.solodatalink
and Components.CommonDataLink = CD.CommonDataLink

END

Hi,

triggers are fired once per statement NOT per row. So if you execute
anhy query that will insert more than one row or updates more tahn one
row, you trigger will not cover all your excepted rows to update / to
cover. So you will have to change your trigger to reflect the presence
of more than one row in the Inserted / Deleted tables.

HTH, jens Suessmeyer.

---
http://www.sqlserver2005.de
---

When I run this code as a select statement in query analyzer I only get one
row?
There is only one solodata row per solodatalink.  I'm not sure what changes
I can
make.   I tried changing to get @extn5 from inserted in case it was not 1
yet, but still no result.

Show quoteHide quote

Cynthia (Cynt***@discussions.microsoft.com) writes:
Since I don't know the tables and how they are related, it's difficult
to for to say what is going on. But obviously, if you update compoents
so that trigger fire, not much will happen in SDLineNo, since the
trigger on Compoents does not update ExtN5. (Notice, by the way, that
the IF UPDATE does say anything whether the value changed. It only
tells us if the column was mentioned at all in the SET clause of the
UPDATE statement.)

As Jens pointed out do this. You had some explanation about "only
one sololink" etc. That does not tell me much, so all I can say is
that this is a possible source for error.

If you insist on using variables, you should add this to the beginning
of the trigger:

   IF @@rowcount > 1
   BEGIN
      ROLLBACK TRANSACTION
      RAISERROR('Multi-row operations not supported', 16, 1)
      RETURN 
   END

To verify that the trigger actually fires, add a dummy PRINT or some-
thing tha proves it executes.

You can also try:

   SELECT name, object_name(parent_obj)
   FROM   sysobjects
   WHERE  Objectproperty(id, 'ExecIsTriggerDisabled')

to see if you have any disabled triggers in the database.

Identity or GUID?
How to add separator blank rows by SQL Query?
Comparing dates in one field
Identity Columns - Design Question
User defined fields via application
Access "inserted" / "deleted" from stored procedure
Obtain values from different tables
How can I update the col value using extended stored procedure
Dynamic View
Using LIKE operator and spacing to search SPs