December 9, 2008 at 2:15 pm
Matt, it works ! I appreciate your help.
Moreso, I appreciate clueing me into the reason for the coding. I'm saving this for future research.
-uman
December 12, 2008 at 12:55 pm
Hello, (Matt, Gail, and the rest of the pros!)
I'm back, and I have written a trigger. It works given your instructions.
However, the underlying table has 2 columns which are defined as primary keys. In any row in which I change the data, the updates are applied on the data table, and returns the approriate trigger data in the same table. For example:
Primary Keys - a, c (these cannot be updated --- this is okay)
Trigger field - d
Update field - b (user needs to be able to update this field)
STATE OF TABLE BEFORE THE UPDATE
abcd
__________________________________________
1a112:05
1b112:05
1c212:05
1d212:05
STATE OF TABLE AFTER UPDATE (a in Column B changed to x)
abcd
__________________________________________
1x112:10
1b112:10
1c212:05
1d212:05
Column d changes the time to 12:10 as a result of the trigger. However, the update also cause the second row to change to 12:10 as well. I do not know how to prevent this. But you will notice that column a and c are the primary keys and the data in a and c are identical. When a and c are not indentical, the change does not take place on d. (see rows 3 and 4 where a = 1 and c = 2), and the data in those rows remain as 12:05.
Here is the Trigger code:
ALTER TRIGGER [dbo].[trTest]
ON [dbo].[NotesF_Devices]
AFTER UPDATE
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
--lastupdate is column d
UPDATE [NotesF_Devices] SET lastupdate = getdate()
FROM [NotesF_Devices]
INNER JOIN inserted on
[NotesF_Devices].b = inserted.b
Any suggestions to resolve this? Once again, much thanks for any help you can provide.
-uman
December 12, 2008 at 1:14 pm
ALTER TRIGGER [dbo].[trTest]
ON [dbo].[NotesF_Devices]
AFTER UPDATE
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
--lastupdate is column d
UPDATE [NotesF_Devices] SET lastupdate = getdate()
FROM [NotesF_Devices]
INNER JOIN inserted on
[NotesF_Devices].a = inserted.a AND [NotesF_Devices].c = inserted.c
You have to join on the primary key, not the column that has been updated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 12, 2008 at 2:23 pm
GAIL , it works! much thanks.... 🙂
-uman
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply