Solved

SQL 2005 trigger not firing / working

Posted on 2007-03-21
5
160 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
export sql results to csv 6 34
SQL Server 2012 r2 - Sum totals 2 21
convert null in sql server 12 31
SQL - Use results of SELECT DISTINCT in a JOIN 4 12
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

813 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

14 Experts available now in Live!

Get 1:1 Help Now