Solved

SQL 2005 trigger not firing / working

Posted on 2007-03-21
5
158 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
  • 2
  • 2
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 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 125 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 142

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now