Insert / Update Trigger only for one record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert / Update Trigger only for one record

Author

Topic

nick_dkc
Starting Member

25 Posts

Posted - 2010-08-08 : 06:50:39

I had a trigger on a table that contained a fairly simple 4 clause CASE statement to concatenate 4 variables in a table depending on 4 conditions. The CASE statement worked fine - and so I thought did the Trigger, until I realised that the trigger was affecting ALL rows in the table - therefore infinitely firing these concatenations every time one record was updated.

Hence, I needed to find a way to restrict the Trigger to only affect the one record that was changed. I have looked at a lot of guidance and instructions for testing the system insert/delete tables - and thought that I had a solution - but I didn't. Can anyone help:

CREATE TABLE [dbo].[petPicsTEST](
[PetPicID] [int] IDENTITY(1,1) NOT NULL,
[commentDate] [smalldatetime] NULL,
[commentUser] [nvarchar](15) NULL,
[commentText] [nvarchar](max) NULL,
[comments] [nvarchar](max) NULL
) ON [PRIMARY]

INSERT INTO petPicsTest
VALUES (1, 12-Aug-2010, Bill, Test 1, NULL)
VALUES (2, 10-Aug-2010, Fred, Test 2, NULL)
VALUES (3, 22-Aug-2010, Mary, Test 3, NULL)
VALUES (4, 28-Aug-2010, Suzy, Test 4, NULL)

And here's the current (not working) trigger:

CREATE TRIGGER dbo.tg_afterInsertUpdateTEST ON [dbo].[petPicsTest]
AFTER INSERT, UPDATE
AS

DECLARE @petpicID int;
DECLARE @commentDate smalldatetime;
--declare @commentText nvarchar(max);
--declare @comments nvarchar(max);

SELECT @petpicID=i.petpicID from inserted i;
SELECT @commentDate=i.commentDate from inserted i;
--select @commentText=i.commentText from inserted i;
--select @comments=i.comments from inserted i;

if update(commentdate)

UPDATE dbo.petPicsTest

SET comments =

CASE
WHEN (comments IS NULL) AND (commentText IS NULL) THEN CONVERT(CHAR(11), commentDate, 106) + ' at '+ CONVERT(CHAR(5), commentDate, 108)+ ' by ' + commentUser + ' : ' + 'No comments'
WHEN (comments IS NOT NULL) AND (commentText IS NULL) THEN comments + '<br/>' + '----------' + '<br/>' + CONVERT(CHAR(11), commentDate, 106) + ' at '+ CONVERT(CHAR(5), commentDate, 108) + ' by ' + commentUser+ ' : ' + 'No comments'
WHEN (comments IS NULL) AND (commentText IS NOT NULL) THEN CONVERT(CHAR(11), commentDate, 106) + ' at '+ CONVERT(CHAR(5), commentDate, 108)+ ' by ' + commentUser + ' : ' + commentText
WHEN (comments IS NOT NULL) AND (commentText IS NOT NULL) THEN comments + '<br/>' + '----------' + '<br/>' + CONVERT(CHAR(11), commentDate, 106) + ' at '+ CONVERT(CHAR(5), commentDate, 108) + ' by ' + commentUser+ ' : ' + commentText
END

WHERE @petpicID=petPicID
------------------------------

If any kind sould is willing to take a look and guide me through to a solution - it would greatly appreciated.

Thanks
Nick

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-08 : 08:02:54

do you have a column in any of the table to determine which row has been changed and to commit changes only to one particular row?

or do you have any constrains on the table? any FK?

nick_dkc
Starting Member

25 Posts

Posted - 2010-08-09 : 00:36:25

Hi slimt_slimt,

The goal is for the trigger to fire on changes to the commentDate column. petPicID is PK.

Nick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources