After update trigger question - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

After update trigger question

Author

Topic

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-07-30 : 09:21:47

I'm creating a trigger that may need to handle multiple rows being inserted.

If I use something like select * into #I from inserted, is it then ok to add an identity column to this table that I can use to reference each row individually?

Something like this:

alter table #I
add rowNo int identity(1, 1)

Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-30 : 13:46:30

dont your main table have an id column? if yes, then you can access the same column inside inserted table also

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-07-31 : 03:58:01

Unfortunately there isn't a single field which is the primary key, which is why I thought adding an identity column would be the easiest way to loop through each row in my trigger.

I just wondered if this was the best way of doing this.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-31 : 11:53:59

Can you explain what you are trying to achomplish? Sure, you could create an identity column on a temp table, but that will probably perform poorly. There is probably a set based solution that we can help you with if you can describe the issue in more detail.

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-08-03 : 05:08:53

Ok, well basically I have a table which I will apply the trigger to which will be updated with either 1 or more rows. I will then use the values inserted to update another table.

If there is more than one row I also have to check that a certain row was inserted. If so, I have to check if a certain values exist and remove if necessary.

For example, there is a code column in the table and if a row is inserted which has a value like 'P001' I have to remove any rows with a code value in (val1, val2, etc...). Once I have performed the check I can update the other table with the inserted values.

Hopefully this help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 05:18:53

Doesn't sound like you need the identity column at all. Can you post your trigger code ?

KH
[spoiler]Time is always against us[/spoiler]

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-08-03 : 05:32:43

The code I have so far is below:


USE [CONV]
GO
/****** Object:  Trigger [dbo].[CR_UpdateNarrative]    Script Date: 08/03/2009 10:31:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[CR_UpdateNarrative] on [dbo].[WORKINPROGRESS] for insert
as
declare @CaseID int
declare @WipNarrative varchar(300)
declare @ExistingNarrative varchar(max)
declare @NewNarrative varchar(max)
declare @LongFlag bit
declare @RowCount as int
declare @KeyCol int
declare @Counter int
declare @Rows int
set @RowCount = @@rowcount

if @RowCount = 0 return

if @RowCount = 1
begin
	if (select count(*) from inserted i inner join wiptemplate wt on wt.wipcode = i.wipcode where wt.wiptypeid = 'SERSCA' and left(i.shortnarrative, 1) = '*') = 1
	begin
		select @CaseID = caseid, @WipNarrative = shortnarrative from inserted
		if exists (select * from casetext where caseid = @CaseID and texttype = '_B')
		begin
			select @LongFlag = longflag, @ExistingNarrative = case when longflag = 1 then text else shorttext end
			from casetext
			where caseid = @CaseID
			and texttype = '_B'

			if @LongFlag = 1
			begin
					set @NewNarrative = @ExistingNarrative + '; ' + replace(@WipNarrative, '*Fixed fee for ', '') + '; '
					update casetext
					set text = @NewNarrative
					where caseid = @CaseID and texttype = '_B'
			end
			else
				if (len(@ExistingNarrative) + len(@WipNarrative)) < 252
				begin
					update casetext
					set shorttext = shorttext + ' ' + replace(@WipNarrative, '*Fixed fee for ', '') + '; '
					where caseid = @CaseID and texttype = '_B'
				end
				else
					update casetext
					set shorttext = NULL, longflag = 1, text = @ExistingNarrative + '; ' + replace(@WipNarrative, '*Fixed fee for ', '') + '; '
					where caseid = @CaseID and texttype = '_B'
		end
		else
		insert into casetext (caseid, texttype, textno, class, language, modifieddate, longflag, shorttext, text)
		values (@CaseID, '_B', 0, NULL, NULL, NULL, 0, replace(@WipNarrative, '*Fixed fee for ', '') + '; ', NULL)
	end
end
else -- multiple rows
begin
	if (select count(*) from inserted i inner join wiptemplate wt on wt.wipcode = i.wipcode where wt.wiptypeid = 'SERSCA' and substring(i.shortnarrative, 1, 1) = '*') = 1
	begin
		select caseid, wipcode, shortnarrative into #I from inserted where substring(shortnarrative, 1, 1) = '*'

		if (select count(*) from #I where wipcode = 'P001') = 1
		begin
			delete from #I where wipcode in ('P009SC', 'P092SC', 'P9A1SC', 'P9A2SC', 'P9P9SC')
		end

		alter table #I add rowNo int identity(1, 1)

		select @Counter = min(rowNo), @Rows = max(rowNo) from #I

		while @Counter <= @Rows
		begin
				
			select @CaseID = caseid, @WipNarrative = shortnarrative from #I where rowNo = @Counter 
			if exists (select * from casetext where caseid = @CaseID and texttype = '_B')
			begin
				select @LongFlag = longflag, @ExistingNarrative = case when longflag = 1 then text else shorttext end
				from casetext
				where caseid = @CaseID
				and texttype = '_B'

				if @LongFlag = 1
				begin
						set @NewNarrative = @ExistingNarrative + '; ' + replace(@WipNarrative, '*Fixed fee for ', '') + '; '
						update casetext
						set text = @NewNarrative
						where caseid = @CaseID and texttype = '_B'
				end
				else
					if (len(@ExistingNarrative) + len(@WipNarrative)) < 252
					begin
						update casetext
						set shorttext = shorttext + ' ' + replace(@WipNarrative, '*Fixed fee for ', '') + '; '
						where caseid = @CaseID and texttype = '_B'
					end
					else
						update casetext
						set shorttext = NULL, longflag = 1, text = @ExistingNarrative + '; ' + replace(@WipNarrative, '*Fixed fee for ', '') + '; '
						where caseid = @CaseID and texttype = '_B'
			end
			else
			insert into casetext (caseid, texttype, textno, class, language, modifieddate, longflag, shorttext, text)
			values (@CaseID, '_B', 0, NULL, NULL, NULL, 0, replace(@WipNarrative, '*Fixed fee for ', '') + '; ', NULL)	
			
			set @Counter = @Counter + 1
		end
	drop table #I
	end
end

I'm failry new to writing triggers etc so I'm sure there are loads of ways I can do this better.

Thanks for your help!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 05:57:27

Hope i interpret your code correctly. The INSERTED table only exists within the trigger and contain the inserted and updated rows.
So basically the concept is you just treat INSERTED as a table and just INNER JOIN to it.


UPDATE 	ct
SET 	[text]	= CASE WHEN ct.longflag = 1 THEN ct.[text] ELSE ct.shorttext END
		+ '; ' 
		+ REPLACE(i.shortnarrative, '*Fixed fee FOR ', '') + '; '
FROM	casetext ct
	INNER JOIN inserted i	ON	ct.caseid	= i.caseid
WHERE 	ct.texttype 	= '_B'
AND	ct.longflag	= 1

UPDATE 	ct
SET 	shorttext 	= CASE 	WHEN	LEN(ct.shorttext) + LEN(i.shortnarrative) < 252
			 	THEN
					ct.shorttext + ' ' + REPLACE(i.shortnarrative, '*Fixed fee FOR ', '') + '; '
				ELSE
					NULL
				END,
	longflag 	= CASE 	WHEN	LEN(ct.shorttext) + LEN(i.shortnarrative) < 252
			 	THEN	longflag
				ELSE	1
				END,
	[text]		= CASE 	WHEN	LEN(ct.shorttext) + LEN(i.shortnarrative) < 252
			 	THEN	[text]
				ELSE	ct.shorttext + '; ' + REPLACE(i.shortnarrative, '*Fixed fee FOR ', '') + '; '
				END
FROM	casetext ct
	INNER JOIN inserted i	ON	ct.caseid	= i.caseid
WHERE 	ct.texttype 	= '_B'
AND	ct.longflag	<> 1

INSERT INTO casetext (caseid, texttype, textno, class, language, modifieddate, longflag, shorttext, text)
SELECT	i.caseid, '_B', 0, NULL, NULL, NULL, 0, REPLACE(i.shortnarrative, '*Fixed fee FOR ', '') + '; ', NULL)
FROM	inserted i
WHERE	NOT EXISTS
	(
		SELECT	*
		FROM	casetext x
		WHERE	x.case_id	= i.case_id
		AND	texttype 	= '_B'
	)

KH
[spoiler]Time is always against us[/spoiler]

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-08-03 : 06:25:33

Thanks for the reply. So if I'm reading your code correctly, I won't have to worry about checking if there is more than one row because I'll be inner joining onto the INSERTED table?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 06:26:23

quote:
Originally posted by chris_cs

Thanks for the reply. So if I'm reading your code correctly, I won't have to worry about checking if there is more than one row because I'll be inner joining onto the INSERTED table?

Yes. That's the idea

KH
[spoiler]Time is always against us[/spoiler]

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-08-03 : 06:38:14

Ok great.

Thanks very much for your help!

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-08-04 : 03:33:10

Just one other question. If I wanted to check if there was a row with a certain value, and then ignore a set of rows if they exist because of that, would I be best to use the following before I perform the insert/update statements?


if (select count(*) from inserted where wipcode = 'P001') = 1
		begin
			delete from inserted where wipcode in ('P009SC', 'P092SC', 'P9A1SC', 'P9A2SC', 'P9P9SC')
		end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-04 : 03:40:09

yup you can use it or this below. also make sure you delete from main table


if exists(select 1 from inserted where wipcode = 'P001')
begin
			delete t
                        from inserted i
                        join casetext ct
                        on ct.caseid	= i.caseid

                        where i.wipcode in ('P009SC', 'P092SC', 'P9A1SC', 'P9A2SC', 'P9P9SC')
end

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-08-04 : 04:14:34

Ok thanks

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-08-13 : 06:15:33

I just thought I'd do some testing with the re-written trigger but I'm getting an error when trying to alter it. The message is:

The data types ntext and varchar are incompatible in the add operator

Would I have to use UPDATETEXT for this? If so, how could I incorporate this into my trigger as I'm not 100% sure how I would.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-13 : 06:49:09

What version of SQL server are you running?

IF you are on 2005 it is STRONGLY, STRONGLY suggested that you change NTEXT / TEXT datatypes to NVARCHAR(MAX), VARCHAR(MAX) datatypes. They don't have the problems you are experiencing with the old TEXT types

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-08-13 : 09:20:35

I was afraid you might say that.

We're using 2005 but the DB is part of some software supplied to us which we aren't allowed to adjust, only add to i.e triggers SPs etc

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-13 : 10:49:09

that's a shame.

I have a crazy idea (it may not work)

You could encapsulate the inserted table into a derived table and cast to VARCHAR(MAX) -- do your string ops and then cast back to TEXT and update.

Something like


UPDATE 	ct
SET 	[text]	= CASE WHEN ct.longflag = 1 THEN ct.[text] ELSE ct.shorttext END
		+ '; ' 
		+ REPLACE(i.shortnarrative, '*Fixed fee FOR ', '') + '; '
FROM	casetext ct
	INNER JOIN inserted i	ON	ct.caseid	= i.caseid
WHERE 	ct.texttype 	= '_B'
AND	ct.longflag	= 1

UPDATE 	ct
SET 	shorttext 	= CASE 	WHEN	LEN(ct.shorttext) + LEN(i.shortnarrative) < 252
			 	THEN
					ct.shorttext + ' ' + REPLACE(i.shortnarrative, '*Fixed fee FOR ', '') + '; '
				ELSE
					NULL
				END,
	longflag 	= CASE 	WHEN	LEN(ct.shorttext) + LEN(i.shortnarrative) < 252
			 	THEN	longflag
				ELSE	1
				END,
	[text]		= CASE 	WHEN	LEN(ct.shorttext) + LEN(i.shortnarrative) < 252
			 	THEN	[text]
				ELSE	ct.shorttext + '; ' + REPLACE(i.shortnarrative, '*Fixed fee FOR ', '') + '; '
				END
FROM	casetext ct
	INNER JOIN (
		SELECT
			[caseId] AS [caseId]
			, CAST([shortnarrative] AS VARCHAR(MAX)) AS [shortNarrative]
		FROM
			inserted
		)
		i	ON	ct.caseid	= i.caseid
WHERE 	ct.texttype 	= '_B'
AND	ct.longflag	<> 1

INSERT INTO casetext (caseid, texttype, textno, class, language, modifieddate, longflag, shorttext, text)
SELECT	i.caseid, '_B', 0, NULL, NULL, NULL, 0, REPLACE(i.shortnarrative, '*Fixed fee FOR ', '') + '; ', NULL)
FROM	inserted i
WHERE	NOT EXISTS
	(
		SELECT	*
		FROM	casetext x
		WHERE	x.case_id	= i.case_id
		AND	texttype 	= '_B'
	)

However, I'm not convinced that would work. Also I don't know what data types your base tables are packing......

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-08-13 : 11:09:03

Ok

I'll have a play around and let you know how I get on.

Thanks for the suggestion

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-08-18 : 05:00:56

Right, I've had to go back to my original trigger I created because it is working for multiple rows and I need to get this finished.

The only thing that isn't working are the rows that I want excluded. I'm using the code below for this:


if (select count(*) from inserted where wipcode = 'P001') = 1
		begin
			delete from inserted where wipcode in ('P009SC', 'P092SC', 'P9A1SC', 'P9A2SC', 'P9P9SC')
		end

But the rows are still being inserted into the casetext table. This should be so simple and I cannot see why they aren't being excluded.

Any ideas?

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-10-02 : 04:17:55

Hey guys,

I'm still having an issue trying to get my trigger to inhibit certain rows. I've created another trigger that is alot more simple, just to try and get the inhibitor to work. This is shown below:


alter trigger updateNarrativeTest on wipTest for INSERT
as
declare @RowCount as int
declare @Counter int
declare @Rows int
declare @ExistingNarrative varchar(max)
declare @CaseID int
declare @WipNarrative varchar(300)
set @RowCount = @@rowcount

if @RowCount = 0 return

if @RowCount >= 1
begin
	if (select count(*) from inserted i where substring(i.shortnarrative, 1, 1) = '*') >= 1
	begin
		select caseid, wipcode, shortnarrative into #I from inserted where substring(shortnarrative, 1, 1) = '*'
		if exists(select 1 from inserted where wipcode = 'P001')
		begin
			delete t
            from #I i
            join narrativeTest ct
            on ct.caseid	= i.caseid
            where i.wipcode in ('P009SC', 'P092SC', 'P9A1SC', 'P9A2SC', 'P9P9SC')
		end

		alter table #I add rowNo int identity(1, 1)

		select @Counter = min(rowNo), @Rows = max(rowNo) from #I

		while @Counter <= @Rows
		begin
			select @CaseID = caseid, @WipNarrative = shortnarrative from #I where rowNo = @Counter
			if exists (select * from narrativeTest where caseid = @CaseID)
			begin
				select @ExistingNarrative = narrative from narrativeTest where caseid = @CaseID
				update narrativeTest
				set narrative = @ExistingNarrative + '; ' + @WipNarrative
				where caseid = @CaseID
			end
			set @Counter = @Counter + 1
		end
		drop table #I
	end
end

I then insert some data into the table which has the trigger as below:


begin tran
insert into wipTest values (123, 'P001', 'P001 text')
insert into wipTest values (123, 'P009SC', '*P009SC text')
insert into wipTest values (123, 'P002', '*P002 text')
insert into wipTest values (123, 'P003', '*P003 text')
commit

I'm trying to prevent the text from the second record being inserted into the narrativeTest table but cannot see why it isn't working.

Any help would be much appreciated.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources