delete multi rows trigger - 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

SQL Server 2000 Forums

SQL Server Development (2000)

delete multi rows trigger

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-16 : 00:18:53

hey all

if this does update on multiple rows in a trigger

update tbl
set fld1 = i.fld1 ,
fld2 = fld2
from tbl t
join inserted i
on i.pk1 = t.pk1
and i.pk2 = t.pk2
join deleted d
on d.pk1 = t.pk1
and d.pk2 = t.pk2

what is the format of the delete on multiple rows?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-16 : 01:31:27

What type of trigger are you trying to produce (Insert, Update and/or Delete)? And what do you want the trigger(s) to accomplish?

I don't see why you are joining your base table to BOTH the Inserted and Deleted virtual tables. This would be a logical thing to do under some circumstances (e.g., Auditing) but its not clear that you need to be doing this in this case.

And why are you updating fld1 and fld2 with i.fld1 and i.fld2? This does not cause any data values to change.

If you can explain what you want to accomplish, perhaps we can be a little more helpful.

HTH

=================================================================

Happy Holidays!

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-16 : 17:24:53

Hey bust

well my updates are within a instead of update trigger and i want to perform 2 updates the primary key is made up of 3 values and if 2 of the values are a number do one update otherwise do the normal update

like so
PK (ENTITYID,MINORBUILDNO,MAJORBUILDNO )

UPDATE COMMON_SECTION SET
INCLUDEFILEID = i.INCLUDEFILEID ,
ISPERSISTENT = i.ISPERSISTENT ,
CHANGEDBY = i.CHANGEDBY ,
CHANGEDDATE = i.CHANGEDDATE ,
SYMBOLID = i.SYMBOLID
FROM COMMON_SECTION c
JOIN inserted i on
c.ENTITYID= i.ENTITYID AND c.MAJORBUILDNO = i.MAJORBUILDNO AND
c.MINORBUILDNO = i.MINORBUILDNO

/*if minor and major build number are = 0*/

UPDATE COMMON_SECTION SET
INCLUDEFILEID = i.INCLUDEFILEID ,
ISPERSISTENT = i.ISPERSISTENT ,
CHANGEDBY = USER ,
CHANGEDDATE = GETDATE(),
SYMBOLID = i.SYMBOLID
FROM COMMON_SECTION c
JOIN inserted i on
c.ENTITYID= i.ENTITYID AND c.MAJORBUILDNO = i.MAJORBUILDNO AND
c.MINORBUILDNO = i.MINORBUILDNO
WHERE c.MAJORBUILDNO = 0 AND c.MINORBUILDNO = 0

so if MAJORBUILDNO and MINORBUILDNO = 0 then they do 2nd update i wanted to put these in a if statment but doesnt really work(not sure how but works so far)

so i dont need to join with deleted tables as well do i? just the inserted one? ( it is all to handle mutliple row update statments and to reproduce within the trigger)

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-16 : 17:26:19

so if i wanted to do a simlar action from a instead of delete trigger
(i.e handle multi row delete statments from a trigger)
would the format be

if emp's pk was id

DELETE from dbo.emp
from dbo.emp JOIN deleted on emp.id = deleted.id
where
emp.name = deleted.name AND
emp.dept_id = deleted.dept_id AND
emp.salary = deleted.salary

brandonl
Yak Posting Veteran

58 Posts

Posted - 2004-12-16 : 18:16:19

I have a table that indicates if a record is active or not. Perhaps you could code it in your app to only look for the active flag? You may want to have the older records, so deleting them may not be a good idea?

~BrandonL

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-17 : 01:38:49

I would certainly think about putting this logic into the application.

If you decide that the logic belongs within the trigger, could you add a where clause to each update statement within the trigger?

UPDATE COMMON_SECTION SET
INCLUDEFILEID = i.INCLUDEFILEID ,
ISPERSISTENT = i.ISPERSISTENT ,
CHANGEDBY = i.CHANGEDBY ,
CHANGEDDATE = i.CHANGEDDATE ,
SYMBOLID = i.SYMBOLID
FROM COMMON_SECTION c
JOIN inserted i on
c.ENTITYID= i.ENTITYID AND c.MAJORBUILDNO = i.MAJORBUILDNO AND
c.MINORBUILDNO = i.MINORBUILDNO
WHERE not (i.MAJORBUILDNO = 0 and i.MINORBUILDNO = 0)

Drop the 'not' from the where clause for the second update.

HTH

=================================================================

Happy Holidays!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources