Trigger not working - 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

Trigger not working

Author

Topic

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-04-17 : 09:12:29

I have created a trigger which is shown below:


ALTER trigger [dbo].[trg_clearNarrative_cr] on [dbo].[WORKHISTORY] for insert
as
declare @rc as int
declare @caseid int
set @rc = @@rowcount

if @rc = 0 return

if @rc > 0
begin
	if (select count(*) from inserted where transtype = 510) >= 1
	begin
		select distinct @caseid = caseid from inserted
		if exists(select * from casetext where caseid = @caseid and texttype = '_B')
		begin
			delete from casetext where caseid = @caseid and texttype = '_B'
		end
	end
end

It doesn't delete the corresponding row from the casetext table and I'm a little bemused as to why.

Can anyone see why I would be having a problem?

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-17 : 09:27:26

select distinct @caseid = caseid from inserted
what is that trying to do?
select top 1 @caseid = caseid from inserted

maybe you want
delete casetext
from casetext c
join (select distinct caseid from inserted) i
on c.caseid = i.caseid
where texttype = '_B'

Get rid of everything else in the trigger.
You can put a
if exists (select * from inserted)
before it if you wish.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-17 : 09:28:29

Make the code work for more than one inserted record.

ALTER TRIGGER	dbo.trg_clearNarrative_cr
ON		dbo.WORKHISTORY
AFTER		INSERT
AS

SET NOCOUNT ON

DELETE		ct
FROM		CaseText AS ct
INNER JOIN	inserted AS i ON i.CaseID = ct.CaseID
WHERE		i.TransType = 510
		i.TextType = '_B'
GO

E 12°55'05.63"
N 56°04'39.26"

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-04-17 : 09:47:51

Thanks for the help.

This is working now after using top 1 instead of distinct. I'll take your other points on board and try to tidy up the code.

Thanks again

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-17 : 09:52:49

That will only cater for a single caseid - if there are more in the inserted table they will be ignored.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-04-17 : 10:18:43

Yeah thats fine as there will only ever be one caseid.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-17 : 11:00:57