[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL 2005 trigger not firing / working

Posted on 2007-03-21
5
Medium Priority
?
166 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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 ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

650 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