Trigger For Delete - Multiple Row Value

Question

Post reply

Trigger For Delete - Multiple Row Value

ard_dicted

SSC-Addicted

Points: 445
More actions
April 21, 2010 at 2:12 am

#221557

hi, i have a question, how to read 'value from deleted' from multiple row in trigger for delete?
select @id = id from deleted <---- can only read single item
update somewhere
set count = count - 1
where idsomewhere = @id
(this code above only affected the first id)
thank you..

Viewing 15 posts - 1 through 15 (of 17 total)

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

shen-dest SSCrazy Points: 2498 More actions · Answer 1

hi,

you may use cursor to get all values deleted.

Regards,
MShenel

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

You can use a simple join to do this

UPDATESW

SETcount = count - 1

FROMsomewhere SW

INNER JOIN deleted D ON SW.idsomewhere = D.id

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ard_dicted SSC-Addicted Points: 445 More actions · Answer 3

what should i put in where clause?

i tried

where id = @id

and it didnt work too..

i see in some discussion (http://www.bigresource.com/Tracker/Track-ms_sql-Dm8l6gPv/)

that we should use IN syntax, i tried in the cursor

where id IN (@id)

and didnt work too..

any help appreciated thx

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

If you want to do an UPDATE for all the deleted rows, the query provided by me will work fine. You don't need to put any Where Clause.

If you want to filter the rows, you can use a Where Clause and it would be better if you post your table structure and some detailed description of your issue.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ard_dicted SSC-Addicted Points: 445 More actions · Answer 5

Kingston Dhasian (4/21/2010)
You can use a simple join to do this
UPDATESW
SETcount = count - 1
FROMsomewhere SW
INNER JOIN deleted D ON SW.idsomewhere = D.id

oh just saw your code, thank you so much, works like magic!!

never thought that we can join with 'deleted'..

thx u so much..

ard_dicted SSC-Addicted Points: 445 More actions · Answer 6

hmm, how about this problem?

i am pairing each id and count how many times they used together...

ID1 ID2 TOGETHER

1 2 5

1 3 4

1 4 3

2 3 5

2 4 2

3 4 1

when I delete a row which use id 2, 3, and 4, i want to recount how many times they used together..

it should be like this :

ID1 ID2 TOGETHER

1 2 5

1 3 4

1 4 3

2 3 4

2 4 1

3 4 0

how should i put it on the trigger??

i am trying to get each id from deleted, but still no luck, any help pls?

thx..

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 7

It would be of great help if you provide the table structure of the table on which you are creating the trigger and the table on which you are doing the count update.

If you do this and explain the problem with sample data i am sure you will get solutions in no time.

Have a look at the following article on how to post data on a forum to get the best help

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ard_dicted SSC-Addicted Points: 445 More actions · Answer 8

Okay.. I will explain it as best as I could..

------

Items

------

IdItem*

ItemName

(Etc)

-----------

Transaction

-----------

IdTrans*

Date

(Etc)

----------

DetailTrans

-----------

IdTrans**

IdItem**

---------

Summary

---------

IdItem1

IdItem2

CountUsedTogether

Note. Summary.CountUsedTogether --> for counting how many times item used together..

The trigger I put is on DetailTrans, so when there is a row in Transaction deleted, the trigger at DetailTrans will fired (RI for delete is cascade)..

And on that trigger I want to recount Summary.CountUsedTogether..

Data Example :

---------

Summary

---------

IdItem1 IdItem2 CountUsedTogether

1 2 5

1 3 4

1 4 3

2 3 5

2 4 2

3 4 1

When I delete a transaction which using item with ID 2,3, and 4, I want to update Summary to :

---------

Summary

---------

IdItem1 IdItem2 CountUsedTogether

1 2 5

1 3 4

1 4 3

2 3 4

2 4 1

3 4 0

(row which are pairing item 2 and 3, 3 and 4, 2 and 4 will have their count - 1, coz a transaction deleted)

Thank you,..

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 9

ard_dicted (4/22/2010)
When I delete a transaction which using item with ID 2,3, and 4

Does this mean you are running the query

DELETE FROM DetailTrans WHERE IdItem IN (2,3,4)

And how is DetailTrans related to Summary table, do you have any foreign keys in Summary that map to DetailTrans?

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ard_dicted SSC-Addicted Points: 445 More actions · Answer 10

Kingston Dhasian (4/22/2010)
ard_dicted (4/22/2010)
When I delete a transaction which using item with ID 2,3, and 4
Does this mean you are running the query
DELETE FROM DetailTrans WHERE IdItem IN (2,3,4)

I mean like this,

I insert a transaction into Transaction, its id is 7

In this transaction, item used is item with id 2,3, and 4..

So, I also insert into DetailTrans values (IdTrans=>7, IdItem=>2),(IdTrans=>7, IdItem=>3),(IdTrans=>7, IdItem=>4)

Transaction - DetailTrans delete rule is cascade.

So when I delete transaction with id 7, it will automatically delete rows in DetailTrans with IdTrans value = 7.

And what I want to put it in trigger is to recount CountUsedTogether in Summary.

Kingston Dhasian (4/22/2010)

And how is DetailTrans related to Summary table, do you have any foreign keys in Summary that map to DetailTrans?

Not to DetailTrans, this is just a summary table I use to count the CountUsedTogether..

But its value always updated when there DetailTrans rows inserted/deleted.

I already made trigger insert in DetailTrans, so after insertion it will update CountUsedTogether value.

I will explain more about CountUsedTogether..

With same example (transaction with IdTrans 7)

item used is item with id 2,3 and 4..

So I am pairing each of that item..

2 and 3.

2 and 4.

3 and 4.

I update CountUsedTogether value at Summary.

UPDATE Summary SET CountUsedTogether = CountUsedTogether +1 WHERE IdItem1 = 2 AND IdItem2 = 3;

UPDATE Summary SET CountUsedTogether = CountUsedTogether +1 WHERE IdItem1 = 2 AND IdItem2 = 4;

UPDATE Summary SET CountUsedTogether = CountUsedTogether +1 WHERE IdItem1 = 3 AND IdItem2 = 4

And the problem is when I do delete on Transaction, it will automatically delete rows in DetailTrans coz of cascade.

And I am looking for a way in trigger delete to update the CountUsedTogether too, I dont know how to get each Id in deleted,

it should be like

UPDATE Summary SET CountUsedTogether = CountUsedTogether -1 WHERE IdItem1 = 2 AND IdItem2 = 3;

UPDATE Summary SET CountUsedTogether = CountUsedTogether -1 WHERE IdItem1 = 2 AND IdItem2 = 4;

UPDATE Summary SET CountUsedTogether = CountUsedTogether -1 WHERE IdItem1 = 3 AND IdItem2 = 4

Any idea? Thx.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 11

I hope this helps..

UPDATES

SETCountUsedTogether = S.CountUsedTogether - 1

FROMSummary S

INNER JOIN deleted D1 ON S.IdItem1 = D1.IdItem

INNER JOIN deleted D2 ON S.IdItem2 = D2.IdItem

WHERED1.IdTrans = D2.IdTrans

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ard_dicted SSC-Addicted Points: 445 More actions · Answer 12

ard_dicted

SSC-Addicted

Points: 445

April 24, 2010 at 7:32 am

#1157257

thank you so much..

it works..

vidya_pande SSCertifiable Points: 5275 More actions · Answer 13

vidya_pande

SSCertifiable

Points: 5275

April 24, 2010 at 12:45 pm

#1157310

this is awesome support Kingston:-)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

shen-dest (4/21/2010)
hi,
you may use cursor to get all values deleted.

Heh... that'll probably get you fired because of how slow it is. It's also really, really bad advice. One of the worst places to use a cursor or While Loop or any form of RBAR is in a trigger.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)