Question about Triggers

[cpark87]

I am new to using Triggers and have create one using the code below. I need to enhance it thought and do not know how...

CREATE Trigger ti_Filtered on tangram.XPC_Software_MasterList_Combined
FOR Insert, Update
AS

Update XPC_Software_MasterList
Set XPC_Software_MasterList.Sft_Filtered = 1
From tangram.XPC_Software_MasterList_Combined INNER JOIN
tangram.XPC_Software_MasterList ON tangram.XPC_Software_MasterList_Combined.Sft_Path = tangram.XPC_Software_MasterList.Sft_Path
Where (tangram.XPC_Software_MasterList_Combined.sft_filtered = 1) AND (tangram.XPC_Software_MasterList.Sft_Filtered IS NULL OR
tangram.XPC_Software_MasterList.Sft_Filtered = 0)

This trigger does work but I would like to see if someone can provide some direction with the following questions:

1) I only want this trigger to apply if the Sft_Filtered column is changed and no other column in the table.

2) I only want this trigger to apply for the current record that is changed.

Any ideas would be appreciated....

 

[SQLSister]

For the first use the if Update (column name) clause.

For the second you need to join to the inserted psuedotable. This is a table the trigger creates which hold s the records which have been changed (there is also a deleted psuedotable). You want to use a join to this table so that if there is a multiple record update then all the records are updated.


Questions about posting. See faq183-874

 

[cpark87]

I came up with this without needing the the temp table and joining through a common field that will not be updated

CREATE TRIGGER Sft_Filtered
ON tangram.XPC_Software_MasterList_Combined
FOR UPDATE AS
IF UPDATE (Sft_Filtered)
BEGIN
UPDATE tangram.XPC_Software_MasterList
SET tangram.XPC_Software_MasterList.Sft_Filtered = tangram.XPC_Software_MasterList_Combined.Sft_Filtered
FROM tangram.XPC_Software_MasterList INNER JOIN
tangram.XPC_Software_MasterList_Combined ON tangram.XPC_Software_MasterList_Combined.Sft_Path = tangram.XPC_Software_MasterList.Sft_Path
END

Let me know if you see any issues with this. It seems to be working...

 

[cpark87]

Another question.. This trigger does work however I am using a Microsoft Access Project as the front end and I receive the following error in access but not in sql enterprise manager

Key Column Information is incorrect or insuffient.. Too many rows affected by update.