can someone tell me what I am doing wrong

HI all,
I have this trigger on a table
**********
CREATE TRIGGER addtotal ON dbo.ClaimFinancialLoss
AFTER INSERT, UPDATE, DELETE
AS
if update(Ammount)
begin
Declare @.tot int, @.id int
select @.id = ClaimID from inserted
--Print 'Id after insert' +str(@.id)
if exists (select * from deleted)
Begin
select @.id = ClaimID from deleted
--Print 'Id after deleted' +str(@.id)
end
Select @.tot=sum(Ammount) from ClaimFinancialLoss group by ClaimID Having
ClaimID = @.id
update claim set total = @.tot where ClaimID = @.id
end
**************
This updates the claim table. On the claim table I have these two triggers
(they are kept seperate just for simplicity at the moment)
****************
CREATE TRIGGER dateupdated ON dbo.Claim
AFTER INSERT
AS
declare @.id int
select @.id=claimid from inserted
update claim set updated = getdate() where claimid = @.id
*************
CREATE TRIGGER layerchange ON [dbo].[Claim]
FOR INSERT, UPDATE AS
if update(layerid)
select layerid as ins from inserted
select layerid as del from deleted
declare @.id int
select @.id=claimid from inserted
begin
delete from ClaimDeductables where claimid = @.id
insert into claimdeductables SELECT PolLayer.LayerCap,
PolLayer.PayOrder,claim.claimid, Layer.LayerID
FROM Claim INNER JOIN
CP_Covertype ON Claim.LayerID = CP_Covertype.CPCKey
INNER JOIN
Covertype ON CP_Covertype.CTKey = Covertype.CTKey
INNER JOIN
PolLayer ON Covertype.CTKey = PolLayer.CTKey INNER
JOIN
Layer ON PolLayer.layerid = Layer.LayerID
WHERE (Claim.ClaimID = @.id)
raiserror('You have made changes to the layers of this claim',9,1,1)
end
*****************
THe raiserror always fires, when ever I update Claimfinancialloss.ammount.
To me this error should not be raised As I have a if update(Layerid), and I
am not updating the layerid of the claim, only the total (through the
trigger.)
Is having two update triggers on the same table, one of them updating the
claimtable again, causing the layerid column to simulate a be updated.
Thanks
Robertyou need a begin after if update(layerid) because if without begin
looks only at the first statement after the if
example
declare @.x int
select @.x =4
if @.x <> 4
print 'yes'
print'blah'
you see, blah will alway be printed
declare @.x int
select @.x =4
if @.x <> 4
begin
print 'yes'
print'blah'
end
If you use begin and end this won't happen since the if will skip that
whole statement
http://sqlservercode.blogspot.com/|||Without investigating this further, you missed that a trigger is fired
per STATEMENT NOT per ROW, so you better should investigate eliminating
this misdesign.
HTH, jens Suessmeyer.|||HI,
YEp, you hit the nail on the head.
Stupid me, I did have a begin statement after the iff statement, But then
during testing I inserted two select statements after the if statement,
naturally the first select statement was executed as a direct result of the
if statment then the other statments executed as a matter of course.
Thanks
Robert
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1140101176.000869.264260@.g43g2000cwa.googlegroups.com...
> you need a begin after if update(layerid) because if without begin
> looks only at the first statement after the if
> example
> declare @.x int
> select @.x =4
> if @.x <> 4
> print 'yes'
> print'blah'
> you see, blah will alway be printed
>
> declare @.x int
> select @.x =4
> if @.x <> 4
> begin
> print 'yes'
> print'blah'
> end
> If you use begin and end this won't happen since the if will skip that
> whole statement
> http://sqlservercode.blogspot.com/
>|||HI Jens,
Yes I realise that, as I said this is a tempory measure, testing. But
anyway, I was pointed to the fact that I never had my begin statement in the
right place by another post.
Thanks anyway for you valued input
Robert
<Jens.Suessmeyer@.googlemail.com> wrote in message
news:1140101824.576386.320190@.g14g2000cwa.googlegroups.com...
> Without investigating this further, you missed that a trigger is fired
> per STATEMENT NOT per ROW, so you better should investigate eliminating
> this misdesign.
> HTH, jens Suessmeyer.
>