October 7, 2009 at 2:53 am
Hey guys,
I'm testing a trigger that should update a secondary table once some data is inserted on the primary one table. This is working fine, but I have a condition which should prevent certain rows being included in the update. My trigger is below:
ALTER trigger [dbo].[updateNarrativeTest] on [dbo].[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 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 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
You should be able to see that I'm trying to inhibit certain rows from being inserted into the second table. This should be so simple but I cannot see why it isn't working so any help would be greatly appreciated.
October 7, 2009 at 3:43 am
Is it throwing any error? which part of the code is not working? you are saying 'you can not insert the records into the second table' but I see you are updating it. Please be bit more clear.
where substring(shortnarrative, 1, 1) = '*'
Is this where you are saying you are filtering the records and its not working?
---------------------------------------------------------------------------------
October 7, 2009 at 3:51 am
Sorry if I wasn't clear. If I was to use the following insert statement on the table which has the trigger applied:
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 2nd row being inserted into the secondary table because there is a row which has a value of P001. This is what isn't working.
Hopefully that is a little clearer.
October 7, 2009 at 3:51 am
i can see what you are trying to achieve, you might be able to do it differently, can you script the table creation for wiptest and post it here. so we can try and recreate your scenario.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 7, 2009 at 4:03 am
If I'm right in what I think you are trying to do, then either you are testing against the wrong table or you don't have the correct concept of when the trigger fires.
This is your sample data:-
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
Are you assuming that the trigger fires at the "commit"?
If so, then you have the wrong concept... the trigger fires once per insert statement.
If not, then shouldn't the line in the trigger that says this:-
if exists(select 1 from inserted where wipcode = 'P001')
actually say this
if exists(select 1 from wipTest where wipcode = 'P001')
October 7, 2009 at 4:03 am
Here is the table script:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[wipTest](
[caseID] [int] NULL,
[wipCode] [varchar](10) NULL,
[shortNarrative] [varchar](1000) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
I actually have another table in my live system where I'm trying to get this scenario working for. I have issues with that table though in that there are fields with the datatype ntext as opposed to varchar(max). Unfortunately I'm unable to change this though.
However if I can just get the inhibitor working for the scenario we're discusing I should be ok.
October 7, 2009 at 4:06 am
Ian Scarlett (10/7/2009)
If I'm right in what I think you are trying to do, then either you are testing against the wrong table or you don't have the correct concept of when the trigger fires.This is your sample data:-
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
Are you assuming that the trigger fires at the "commit"?
If so, then you have the wrong concept... the trigger fires once per insert statement.
If not, then shouldn't the line in the trigger that says this:-
if exists(select 1 from inserted where wipcode = 'P001')
actually say this
if exists(select 1 from wipTest where wipcode = 'P001')
Exactly! and if P001 is there in the table you are not going to insert any more records or if its only for a group?
---------------------------------------------------------------------------------
October 7, 2009 at 4:09 am
The part of our application this refers to can insert more than one row at a time. Are you saying that even if the user selects say 5 items from the GUI, on the DB end the trigger should only detect each individual row being inserted.
I thought that the 'inserted' table would contain 5 rows and I could remove the rows I didn't need from my #I table. Is this not right?
October 7, 2009 at 4:22 am
The trigger fires only once per INSERT statement received by the database.
So, if your application is generating 1 insert statement for each row the user selects from the GUI, then the trigger will fire several times... one for each insert, and each time the trigger fires, it will only have that row in the INSERTED table.
If you want to insert multiple rows in the same statement, and have the trigger only fire once, you're going to have to resort to something like:-
begin tran
insert into wipTest
SELECT 123, 'P001', 'P001 text'
UNION
SELECT 123, 'P009SC', '*P009SC text'
UNION
SELECT 123, 'P002', '*P002 text'
UNION
SELECT 123, 'P003', '*P003 text'
commitThat way you are only actually executing a single INSERT statement that is inserting multiple rows.
October 7, 2009 at 4:51 am
Ok thanks for the advice on this. It looks like I may have to get back to the drawing board on this one.
I'll let you know how I get on.
October 7, 2009 at 2:41 pm
Can you provide the DDL and some sample data for the narrativeTest table that you are updating in the trigger?
What do you want to do if the row does not exist in narrativeTest?
So you do not want this row to update narrativeTest:
insert into wipTest values (123, 'P009SC', '*P009SC text')
but you do want these rows to update narrativeTest:
insert into wipTest values (123, 'P001', 'P001 text')
insert into wipTest values (123, 'P002', '*P002 text')
insert into wipTest values (123, 'P003', '*P003 text')
I think this code will do the same thing without all the if's and rowcount's and looping:
ALTER trigger [dbo].[updateNarrativeTest] on [dbo].[wipTest] for INSERT
AS
UPDATE narrativeTest
SET narrative = narrative + '; ' + I.shortnarrative
FROM
inserted AS I
WHERE
CASE
WHEN I.shortnarrative LIKE '*%' AND EXISTS (SELECT 1 FROM inserted WHERE wipcode = 'P001') THEN I.wipcode
ELSE '0'
END NOT IN ('P009SC', 'P092SC', 'P9A1SC', 'P9A2SC', 'P9P9SC');
RETURN;
But, without the DDL and test data for narrativeTest I can't really test it thoroughly.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply