For Insert, Update Trigger

Last Post 07 Nov 2008 03:46 PM by SwePeso. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
dmsstaff
New Member
New Member

--
07 Nov 2008 10:20 AM
I have the following trigger to update the last change stamp field with the current date/time if any of the columns in my table are updated, however, when a row is inserted, the last change stamp field should also be updated for the new inserted row only and not the whole table. My code works when I do an update but it doesn't allow me to add new rows. The default value on the last change stamp field is set to "getdate()"

CREATE TRIGGER DetectChange_PAFTest ON dbo.MHSAPAFTest
FOR INSERT, UPDATE
AS
SELECT FormUniqueID FROM deleted
IF @@ROWCOUNT <> 0
BEGIN
UPDATE [dbo].[MHSAPAFTest]
SET LastChangeStamp = getdate()
FROM [dbo].[MHSAPAFTest] t JOIN inserted i
On t.FormUniqueID = i.FormUniqueID
END


Thanks











dmsstaff
New Member
New Member

--
07 Nov 2008 12:34 PM
I have changed my code to the following:

CREATE TRIGGER DetectChange_PAFTest ON dbo.MHSAPAFTest
FOR UPDATE
AS
IF NOT UPDATE(FormUniqueID)
BEGIN
UPDATE [dbo].[MHSAPAFTest]
SET LastChangeStamp = getdate()
FROM [dbo].[MHSAPAFTest] t INNER JOIN inserted i
On t.FormUniqueID = i.FormUniqueID
END

What I need is for the lastchangestamp field to get the current date/time whe a new row is inserted or when any of the columns on an existing row is updated. What I am getting with this trigger is that it updates the lastchangestamp field with the current date/time when I update any of the columns on an existing row. The problem is that when I insert a new row, the lastchangestamp field for all records gets updated and I only need the new inserted record to have the current date/time. When I insert a new row, I am not even touching existing records so the lastchangestamp column shouldn't be updated.

Thanks
















Textexisting
dmsstaff
New Member
New Member

--
07 Nov 2008 02:04 PM
That is exactly my problem, I don't understand why the trigger fires for inserts. According to the following statement "Use a rowcount of the Deleted table to determine whether an Insert (= 0) or an Update (<> 0) fired this particular execution." if you insert a row, the deleted table shouldn't have any data, well in my case, when I print the rowcount for the deleted table on an insert operation, I get the exact same number as I get when I do a rowcount on the inserted table. Is there a settign in SQL that I am missing because it seems to be that update and insert are behaving the same way.

My real table has about 240 fields, I am just including the significant ones here as a sample:


CREATE TABLE [dbo].[MHSAPAFTest] (
[FormUniqueID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[LastChangeStamp] [datetime] NULL ,
[FSTNAME] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LSTNAME] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CCN] [int] NULL ,
[PROVIDER] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[MHSAPAFTest] ADD
CONSTRAINT [DF_MHSA_PAF_Test_FormUniqueID] DEFAULT (newid()) FOR [FormUniqueID],
CONSTRAINT [DF_MHSAPAFTest_LastChangeStamp] DEFAULT (getdate()) FOR [LastChangeStamp],
CONSTRAINT [PK_MHSA_PAF_Test] PRIMARY KEY CLUSTERED
(
[FormUniqueID]
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER TrimProviderPAF_Test ON dbo.MHSAPAFTest
FOR INSERT
AS UPDATE MHSAPAFTest SET PROVIDER = LTRIM(PROVIDER)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER DetectChange_PAFTest ON dbo.MHSAPAFTest
FOR UPDATE AS
DECLARE @rowcnttbl int
DECLARE @rowcntins int
set @rowcntins = (SELECT COUNT(*) FROM inserted)
set @rowcnttbl = (SELECT COUNT(*) FROM MHSAPAFTest)
IF @rowcntins<@rowcnttbl
BEGIN
UPDATE [dbo].[MHSAPAFTest]
SET LastChangeStamp = getdate()
FROM [dbo].[MHSAPAFTest] t INNER JOIN inserted i
On t.FormUniqueID = i.FormUniqueID
END



More to come ...........
dmsstaff
New Member
New Member

--
07 Nov 2008 03:19 PM
quote:

Originally posted by: dmsstaff
That is exactly my problem, I don't understand why the trigger fires for inserts. According to the following statement "Use a rowcount of the Deleted table to determine whether an Insert (= 0) or an Update (<> 0) fired this particular execution." if you insert a row, the deleted table shouldn't have any data, well in my case, when I print the rowcount for the deleted table on an insert operation, I get the exact same number as I get when I do a rowcount on the inserted table. Is there a settign in SQL that I am missing because it seems to be that update and insert are behaving the same way.

I ended up doing the following which is working but it is slow, so any help is appreciated.

CREATE TRIGGER DetectChange_PAFTest ON dbo.MHSAPAFTest
FOR UPDATE AS
DECLARE @rowcnttbl int
DECLARE @rowcntins int
set @rowcntins = (SELECT COUNT(*) FROM inserted)
set @rowcnttbl = (SELECT COUNT(*) FROM MHSAPAFTest)
IF @rowcntins<@rowcnttbl
BEGIN
UPDATE [dbo].[MHSAPAFTest]
SET LastChangeStamp = getdate()
FROM [dbo].[MHSAPAFTest] t INNER JOIN inserted i
On t.FormUniqueID = i.FormUniqueID
END



Thanks



SwePeso
New Member
New Member

--
07 Nov 2008 03:46 PM
This should work

CREATE TRIGGER DetectChange_PAFTest
ON dbo.MHSAPAFTest
FOR INSERT,
UPDATE
AS

SET NOOUNT ON

UPDATE t
SET LastChangeStamp = GETDATE()
FROM dbo.MHSAPAFTest AS t
INNER JOIN inserted AS i ON i.FormUniqueID = t.FormUniqueID


For INSERTs, the inserted virtual table holds all new records.
For UPDATEs, the inserted virtual table holds touched records.
dmsstaff
New Member
New Member

--
17 Nov 2008 12:47 PM
I tried your solution but it works on touched records only but when I add a new record, it updates the whole table with the last change stamp being the current date/time. And I only need the last change stamp to change for touched records or for new inserted records but not for the whole table.


Thanks
You are not authorized to post a reply.

Acceptable Use Policy