SQL Trigger help :/

[poffle]

Howzit,

so im working on this trigger and its pissing me off as i cannot find the correct way to do it:

3 tables involved here:

TblSIID
tblService
tblApplication

Very simple: If the status is updated to 10 for the 1 record (tblSIID) - tblService must be updated.
the relations are tblSiid (contact number) -> tblApplication (contactno)
tblApplication (ApplicationID) -> tblService (ApplicationID).

My Code is as such:

CREATE TRIGGER fUpdate
ON tblSIID
AFTER UPDATE
AS
IF (SELECT TOP 1 Status FROM deleted) = '10'
BEGIN
SET NOCOUNT ON
UPDATE tblService set ssID = '2061'
WHERE ((SELECT TOP 1 ContactNo From Deleted) in
(SELECT TOP 1 ContactNo from tblApplication a
inner join tblService b on a.Applicationid = b.Applicationid))
end

Problem is. If i remove the top1 in bold, it updates the entire service table. If i leave the bold in, it doesnt update.

Any ideas?

 

[FarligOpptreden]

You're using the wrong sub-table for the trigger. Select from the INSERTED table, not the DELETED. DELETED would contain the old value and INSERTED would contain "10", because you are updating from some value to "10". In SQL Server triggers, sub tables called INSERTED and DELETED contain the values modified in the table. For an INSERT statement, only the INSERTED table gains an entry. For DELETE statements, only the DELETED table gains an entry. For UPDATE statements, however, both INSERTED and DELETED tables get modified. The DELETED table contains the old column values, while the INSERTED table contains the new column values.

 

[poffle]

Rad, ill try that now quick

-- does the same thing. if i break the query down, it returns only the single line as it should, thats with the Top1 in bold taken out.

 

[ToxicBunny]

Ok, I'm not up to speed with the structure of the data, but somehow the query doesn't look totally right to me..... it doesn't seem specific enough.

you don't seem to be specifying WHICH contactno/applicationid you want to do the update on.

I would do it as a 3 level join back to tblSIID to get the correct contactno, join onto tblApplication to get the correct application id, and then update tblService based on that applicationid

 

[Vixremento]

Try this. You have foreign keys between the tables so use them to join from the one table to the next.

CREATE TRIGGER fUpdate
ON tblSIID
AFTER UPDATE
AS
SET NOCOUNT ON
UPDATE		tblService SET ssID = '2061'
FROM		Inserted i
INNER JOIN	tblApplication a
ON		i.contactno = a.contactno
INNER JOIN	tblService s
ON		a.ApplicationID = s.ApplicationID
WHERE		i.Status = 10

 

[ToxicBunny]

Thats more like what I was thinking it should be... was just busy fixing a major cock-up to apply my mind properly

 

[Vixremento]

Thats more like what I was thinking it should be... was just busy fixing a major cock-up to apply my mind properly

I know the feeling! Been fixing cock-ups for the last two weeks now...but I think I can see the light - let's just hope these chaps I'm supporting agree (they're quick to say "everything looks sharp, boet!" when you're on site but the moment you leave they find all the "bugs". Pity our whole "CMMI" drive fell through the cracks in our new building.

 

[ToxicBunny]

I know I'll never see the light..... but I've accepted that and just make everyone elses life a complete misery... its more fun that way

 

[poffle]

Try this. You have foreign keys between the tables so use them to join from the one table to the next.

CREATE TRIGGER fUpdate
ON tblSIID
AFTER UPDATE
AS
SET NOCOUNT ON
UPDATE		tblService SET ssID = '2061'
FROM		Inserted i
INNER JOIN	tblApplication a
ON		i.contactno = a.contactno
INNER JOIN	tblService s
ON		a.ApplicationID = s.ApplicationID
WHERE		i.Status = 10

Yus bru, thank you so much. I didnt realise i could use FROM in an update statement You saved my life. Dankie baie baie

 

[Vixremento]

Yus bru, thank you so much. I didnt realise i could use FROM in an update statement You saved my life. Dankie baie baie

Glad I could help!