Trigger

Question

Trigger

Adam Sandler

SSCrazy

Points: 2697
More actions
June 5, 2007 at 8:33 am

#103370

CREATE TRIGGER ContactNames_IU ON dbo.ContactNames
FOR INSERT, UPDATE

AS
If Update(Inactive)
BEGIN
    If Inactive = 1
    BEGIN
     DELETE ContactResp WHERE kContactNameID In(SELECT ContactNameID FROM Inserted)
    END
END

I am trying to write a trigger which does the following -

For each ContactNameID (PK) in contact names which has just has the inacive field set to 1. (bit)

I want to delete all the records in table ContactResp where the kContactNameID = ContactNameID

The bit I am having trouble with is only doing this for records that have had the Inactive set to 1

Maybe -

AS
If Update(Inactive)
BEGIN
     DELETE ContactResp WHERE kContactNameID In(SELECT ContactNameID FROM Inserted WHERE Inactive = 1)
END

But say 10 records where updated and only 5 of them had Inactive changed , and only one of them had incative changed to 1.

I only want to act on that 1 record? Would this do this or would it act on any updated record that has inactive = 1 (regardless of whether incative had changed)

Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 1

It is my understanding that the if UPDATE(columnname) evalutes to true if any record in the result set had that column modified/inserted. So your delete statement would include all rows where the inactive column value equals 1 whether it changed or not, unless none of the records had that column value change.

However your can use the following:

DELETE ContactResp

WHERE kContactNameID In(SELECT ContactNameID

FROM Inserted i Join contactnames cn on (i.pk = cn.pk)

WHERE i.inactive = 1 and cn.Inactive <> 1)

putting it inside an IF UPDATE() statement just prevents it from executing unnecessarily, but even outside that statement it will still accomplish exactly what you want. It joins the "inserted" table back to the base table and finds only where inactive has changed to 1.

HTH,

James.

Adam Sandler SSCrazy Points: 2697 More actions · Answer 2

Cheers James very helpful - just one think what is PK?

i.pk = cn.pk?

Thats the primary key for the record? and that join only brings out those in i and joins the relevent cn records?

Thanks again

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 3

Sorry, PK is short for "Primary Key" (if the table PK is a composit of two or more columns adjust the join statement accordingly)

Since I did not know the actual name of the PK column in your table I just used "PK".

Also I may have been to hasty in my answer, it's been a few months since I worked with triggers. I think you may want to join to the "deleted" table rather than the base table. The deleted table holds the "Original" records while the base table has already been altered. Give me a minute to test something and I'll post updated code.

James.

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 4

Ok, I wanted to make sure. Yes you need to alter the code I posted as follows:

DELETE ContactResp

WHERE kContactNameID IN (SELECT ContactNameID

FROM inserted i JOIN deleted d on (i.pk = d.pk)

WHERE i.inactive = 1 and d.inactive <> 1)

Another consideration is newly inserted records. If you want to act on those records then the following should be considered:

--When "deleted" is empty then an insert occurred

--When "inserted" is empty a delete occurred

--When neither is empty then an update occurred

if exists (select 1 from inserted) and not exists (select 1 from deleted)

begin

DELETE ContactResp

WHERE kContactNameID IN(SELECT ContactNameID FROM deleted d WHERE d.inactive = 1)

end

--James.

Adam Sandler SSCrazy Points: 2697 More actions · Answer 5

OK Thanks - I think I have the update part OK.

But the insert bit -

Another consideration is newly inserted records. If you want to act on those records then the following should be considered:

--When "deleted" is empty then an insert occurred

--When "inserted" is empty a delete occurred

--When neither is empty then an update occurred

if exists (select 1 from inserted) and not exists (select 1 from deleted)

begin

DELETE ContactResp

WHERE kContactNameID IN(SELECT ContactNameID FROM deleted d WHERE d.inactive = 1)

end

Should this be - FROM inserted i where i.inactive = 1

??

Cheers

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 6

Oops!

Your right switch back to the "inserted" table. That what I get for not reviewing what I just posted. I had the "deleted" table on my mind, as it should be empty during inserts. Just goes to show you how important thorough testing is (or at least code review sessions!).

James.

Adam Sandler SSCrazy Points: 2697 More actions · Answer 7

Adam Sandler

SSCrazy

Points: 2697

June 6, 2007 at 7:44 am

#711113

Excellent replies . Thankyou