The Basics of a Trigger 2

[DerickD]

I have done a search for Trigger questions an answers and there are lots... but I just can not get it working.

I have a simple table with 3 fields:
ID(autonumber);Value1;Value2

I want Value2 to always be 100 higher than Value1.

So :

CREATE TRIGGER ValueTwo ON testTrigger
FOR INSERT
AS
UPDATE testTrigger
SET Value2 = Value1 + 100
WHERE ID in (Select ID from inserted)


But When I run this and insert a value, it updates ALL Value2's to be 100 + (my new Value1)

Where am I going wrong?

Thanks,
DerickD

 

[eramgarden]

"inserted" table has many rows..not just one row...

try this:
CREATE TRIGGER ValueTwo ON testTrigger
FOR INSERT
AS
UPDATE testTrigger
SET Value2 = Value1 + 100
from inserted , testtrigger
WHERE testtriger.ID=inserted.id

 

[DerickD]

It still does not work ....

I used :

CREATE TRIGGER ValueTwo ON testTrigger
FOR INSERT
AS
UPDATE testTrigger
SET Value2 = testtrigger.Value1 + 100
from inserted , testtrigger
WHERE testtrigger.ID=inserted.ID

as it returned Value1 as Ambiguous,


What is going on....When I insert a new value, it still updates ALL Value2

 

[nigelrivett]

There's nothing wrong with your original trigger.
Are you sure you don't have another trigger on the table from a previous attempt?

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

If you want a version using a join

CREATE TRIGGER ValueTwo ON a
FOR INSERT
AS
UPDATE testTrigger
SET Value2 = t.Value1 + 100
from testTrigger t
join inserted i
on i.ID = t.ID

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[DerickD]

As this was my first attempt at working with triggers, I have learnt a valuable lesson :


Make Sure You Delete Unwanted Triggers.


It works fine now!!
Thanks alot nigelrivett, it sound as if you speak from experience