?
Solved

SQL 2005 trigger not firing / working

Posted on 2007-03-21
5
Medium Priority
?
165 Views
Last Modified: 2008-03-03
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?
0
Comment
Question by:Drammy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18763073
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 500 total points
ID: 18763128
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
 

Author Comment

by:Drammy
ID: 18763247
No there are no updates to the PK.

Am I supposed to accept both solutions?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18763320
You should refer to the EE rules about that (note: I don't really like that "hard" rule to accept the first answer)...
0
 

Author Comment

by:Drammy
ID: 18763394
Thanks for the help guys
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question