Can't get update trigger working

[Jen123]

I am trying to update a table when a change has been made to a secondary table. Can anyone spot where I am going wrong? Someone please help!!

CREATE TRIGGER JenContactDateUpdate ON [dbo].[tblFees]
FOR UPDATE
AS
UPDATE tblCompanies
SET DateUpdated = getDate()
FROM tblCompanies INNER JOIN tblFees on tblCompanies.CompanyRef=tblFees.CompanyRef
WHERE tblCompanies.CompanyRef =tblFees.CompanyRef

 

[Jen123]

Maybe I should add to this, in that currently the trigger is updating all records in tblCompanies as opposed to just the related record.

J

 

[osjohnm]

Hi Jen

You are not limiting the results to an indivdual record therefore on every update all the records that match will be updated. Rather use the "virtual" inserted table which is used with triggers to store the actual record which ahs changed like so:

CREATE TRIGGER JenContactDateUpdate ON [dbo].[tblFees]
FOR UPDATE
AS
UPDATE tblCompanies
SET DateUpdated = getDate()
FROM tblCompanies INNER JOIN inserted on tblCompanies.CompanyRef=inserted.CompanyRef
WHERE tblCompanies.CompanyRef =inserted.CompanyRef

If that doesn't work you might have to join tblFees onto the inserted table and tblCompanies.

Hope this helps

John

 

[Jen123]

Me again

Well thanks for your advice. I've tried the first way and joining tbl fees in too which still doesn't appear to be working, this is what I've ended up with

CREATE TRIGGER JenContactDateUpdate ON [dbo].[tblFees]
FOR UPDATE
AS
UPDATE tblCompanies
SET DateUpdated = getDate()
FROM tblCompanies INNER JOIN inserted on tblCompanies.CompanyRef=inserted.CompanyRef INNER JOIN tblFees on inserted.CompanyRef=tblFees.CompanyRef
WHERE tblCompanies.CompanyRef =inserted.CompanyRef

Anyway, I originally tried something quite simple that said
WHERE tblCompanies.companyRef = 3392

Which still didn't work??

 

[osjohnm]

hi

I looked at your code but I don't use ANSI very often (I know its a bad habit)so I'm not sure if you are joining correctly or not but here is how the trigger should look in old style SQL :

FOR UPDATE
AS
UPDATE tblCompanies
SET DateUpdated = getdate()
FROM tblCompanies, tblFees, inserted
where inserted.companyref = tblFees.CompanyRef
and tblFees.CompanyRef = tblCompanies.CompanyRef

You say that even with your joining it isn't working?
Is it still updating all the records? Are you getting error messages?

Try creating a sql statement that selects the row that was affected first and if that works then try and setup the update statement?

Maybe you can post both the structures of tblFees and tblCompanies?

John

 

[tlbroadbent]

You've made the selection criteria to complex and redundant. If CompanyRef is unique then the following should work.

CREATE TRIGGER JenContactDateUpdate ON [dbo].[tblFees]
FOR UPDATE
AS

UPDATE tblCompanies
SET DateUpdated = getDate()
FROM tblCompanies
INNER JOIN inserted
on tblCompanies.CompanyRef=inserted.CompanyRef

Is CompanyRef the primary key of tblCompanies? Are addtional columns required to uniquely identify a row? If so, do those other columns exist on tblFees? All columns required to identify a matching record should be included in the JOIN clause. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.