SQL 2005 trigger not firing / working

SQL 2005:

I have a datLastUpdate field on all my tables in a SQL db.  I then have triggers which update this field every time the corresponding row is INSERTED / UPDATED.

The strange thing is it is failing on only one table, the other 13 or so work fine.  Here is an example of a table and trigger that does work:

CREATE TABLE [dbo].[tblActivity](
      [strActivityPK] [varchar](30) COLLATE Latin1_General_CI_AS NOT NULL,
      [strActivityCode] [varchar](60) COLLATE Latin1_General_CI_AS NULL,
      [strActivityDescription] [varchar](60) COLLATE Latin1_General_CI_AS NULL,
      [datTarget] [datetime] NULL,
      [datActual] [datetime] NULL,
      [intMatterIntCode] [int] NULL,
      [intRecurringActivityFlag] [int] NULL CONSTRAINT [DF_tblActivity_intRecurringActivityFlag]  DEFAULT ((0)),
      [datLastUpdate] [datetime] NULL,
 CONSTRAINT [PK_tblActivity] PRIMARY KEY CLUSTERED
(
      [strActivityPK] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

create trigger [trgUpdateActivity] on [dbo].[tblActivity] for insert, update
as
update tblActivity
set
    datLastUpdate = getdate()
from inserted i left outer join deleted d on i.strActivityPK = d.strActivityPK
where tblActivity.strActivityPK = i.strActivityPK



And here is the problematic table and trigger:

CREATE TABLE [dbo].[tblMatter](
      [intMatterIntCode] [int] NOT NULL,
      [strMatterCode] [varchar](60) COLLATE Latin1_General_CI_AS NULL,
      [strMatterTypeCode] [varchar](4) COLLATE Latin1_General_CI_AS NULL,
      [intVPIMatterIntCode] [int] NULL,
      [strVPIMatterCode] [varchar](60) COLLATE Latin1_General_CI_AS NULL,
      [intParentMatterIntCode] [int] NULL,
      [strVPIName] [varchar](45) COLLATE Latin1_General_CI_AS NULL,
      [intAgentEntityCode] [int] NULL,
      [intPanelMemberEntityCode] [int] NULL,
      [intSellerEntityCode] [int] NULL,
      [intPropertyEntityCode] [int] NULL,
      [intBSDEntityCode] [int] NULL,
      [datInstruction] [datetime] NULL,
      [datReceived] [datetime] NULL,
      [datAllocated] [datetime] NULL,
      [datAccepted] [datetime] NULL,
      [datSettled] [datetime] NULL,
      [datClosed] [datetime] NULL,
      [datAborted] [datetime] NULL,
      [datLastUpdate] [datetime] NULL,
 CONSTRAINT [PK_tblMatter] PRIMARY KEY CLUSTERED
(
      [intMatterIntCode] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE trigger [trgUpdateMatter] on [dbo].[tblMatter] for insert, update
as
update tblMatter
set
    datLastUpdate = getdate()
from inserted i left outer join deleted d on i.intMatterIntCode = d.intMatterIntCode
where tblMatter.intMatterIntCode = i.intMatterIntCode



Anyone got any ideas why this isn't working?
DrammyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
CREATE trigger [trgUpdateMatter] on [dbo].[tblMatter] for insert, update
as
update tblMatter
set  datLastUpdate = getdate()
from tblMatter t
join inserted i
on i.intMatterIntCode = t.intMatterIntCode
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
do you allow updates to the primary key?

but anyway it would be safer to do either angeliii's suggestion (just use inserted)

or

CREATE trigger [trgUpdateMatter] on [dbo].[tblMatter] for insert, update
as
update tblMatter
set
    datLastUpdate = getdate()
from inserted i
left outer join deleted d on i.intMatterIntCode = d.intMatterIntCode
0
DrammyAuthor Commented:
No there are no updates to the PK.

Am I supposed to accept both solutions?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You should refer to the EE rules about that (note: I don't really like that "hard" rule to accept the first answer)...
0
DrammyAuthor Commented:
Thanks for the help guys
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.