using triggers in sql 2000 [Resolved]

[Oliver1]

Okay what I'd like to do is when a value is updated in one table I would like to add a value into another table

CREATE TRIGGER dbo.gmprospectdate
ON contact1
FOR UPDATE
AS
DECLARE @key1 varchar(255), @ACCOUNTNO varchar(20)

SELECT @key1 = key1, @ACCOUNTNO=ACCOUNTNO
FROM inserted 
where key1 is not null

case  @key1
when 'Prospect' then update contact2 set uprospect = getdate() where ACCOUNTNO = @ACCOUNTNO
when 'Trial' then update contact2 set UFCSDATE = getdate() where ACCOUNTNO = @ACCOUNTNO
when 'Client' then update contact2 set USTARTDATE = getdate() where ACCOUNTNO = @ACCOUNTNO
end

This is what I've come up with and it obviuosly doesn't work, could you give me some pointers.
Cheers all.

[brucevde]

Never assume the Inserted table has only one record. The Select statement will only return one Key/AccountNo and so you may miss records.

A simple Update statement should do the trick. This is off the top of my head so the syntax may be off.

Update Contact2 Set
   uprospect = Case When Inserted.Key1 = 'Prospect' Then GetDate() Else uprosect End,
   UFCSDATE = Case When Inserted.Key1 = 'Trial' Then GetDate() Else UFCSDATE End,
   USTARTDATE = Case When Inserted.Key1 = 'Client' Then GetDate() Else USTARTDATE End,
From Contact2 Inner Join 
Inserted On Contact2.AccountNo = Inserted.AccountNo And Inserted.Key1 Is Not Null

[Oliver1]

Thanks, the below works a treat.

CREATE TRIGGER dbo.gmupstaus
ON contact1
for update 
as
Update Contact2 Set
   uprospect = Case When Inserted.Key1 = 'Prospect' and uprospect is null Then GetDate() Else uprospect End,
   UFCSDATE = Case When Inserted.Key1 = 'Trial' and UFCSDATE is null  Then GetDate() Else UFCSDATE End,
   USTARTDATE = Case When Inserted.Key1 = 'Client' and USTARTDATE is null  Then GetDate() Else USTARTDATE End
From Contact2 Inner Join 
Inserted On Contact2.AccountNo = Inserted.AccountNo And Inserted.Key1 Is Not Null