multi row update from instead of trigger

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)

multi row update from instead of trigger

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-15 : 19:09:17

Hello everyone,

I know i have asked similar questions in previous posts but i would be forever appricative of the person who can answer this one

i am converting a interbase database to sql server and one of my triggers from interbase was a before update trigger that would change the value of 4 of the update values if MajorBuildNo and
MinorBuildNo = 0

so i am trying to create a trigger that if MajorBuildNo and MinorBuildNo = 0 does a differn't update otherwise it would perform the normal update that would of be done.

this works fine if there is only 1 table row updated but i dont know how to code this when mutliple table rows are upated (maybe join the inserted table with the acutal table?) but i still dont know how to do the test conidtion (the IF) with mutliple row updates

please help, as its driving me insane :)

CREATE TRIGGER CHANGEENUMERATED ON ENUMERATED
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
IF ((SELECT MajorBuildNo FROM inserted) = 0 AND (SELECT MinorBuildNo FROM inserted) = 0)
BEGIN
UPDATE ENUMERATED SET ENUMID = (SELECT ENUMID FROM inserted),
MAJORBUILDNO = 0,
MINORBUILDNO = 0,
ENUMITEMNAME = (SELECT ENUMITEMNAME FROM inserted),
VALUEOFITEM = (SELECT VALUEOFITEM FROM inserted),
DESCRIPTION = (SELECT DESCRIPTION FROM inserted),
ENTITYID = (SELECT ENTITYID FROM inserted),
CHANGEDBY = USER,
CHANGEDDATE = GETDATE()
WHERE ENUMID = (SELECT ENUMID FROM deleted) AND
MAJORBUILDNO = (SELECT MAJORBUILDNO FROM deleted) AND
MINORBUILDNO = (SELECT MINORBUILDNO FROM deleted) AND
ENUMITEMNAME = (SELECT ENUMITEMNAME FROM deleted) AND
VALUEOFITEM = (SELECT VALUEOFITEM FROM deleted) AND
DESCRIPTION = (SELECT DESCRIPTION FROM deleted) AND
ENTITYID = (SELECT ENTITYID FROM deleted) AND
CHANGEDBY = (SELECT CHANGEDBY FROM deleted) AND
CHANGEDDATE = (SELECT CHANGEDDATE FROM deleted)
END
ELSE /* perform normal update query */
BEGIN
UPDATE ENUMERATED SET ENUMID = (SELECT ENUMID FROM inserted),
MAJORBUILDNO = (SELECT MAJORBUILDNO FROM inserted),
MINORBUILDNO = (SELECT MINORBUILDNO FROM inserted),
ENUMITEMNAME = (SELECT ENUMITEMNAME FROM inserted),
VALUEOFITEM = (SELECT VALUEOFITEM FROM inserted),
DESCRIPTION = (SELECT DESCRIPTION FROM inserted),
ENTITYID = (SELECT ENTITYID FROM inserted),
CHANGEDBY = (SELECT CHANGEDBY FROM inserted),
CHANGEDDATE = (SELECT CHANGEDDATE FROM inserted)
WHERE ENUMID = (SELECT ENUMID FROM deleted) AND
MAJORBUILDNO = (SELECT MAJORBUILDNO FROM deleted) AND
MINORBUILDNO = (SELECT MINORBUILDNO FROM deleted) AND
ENUMITEMNAME = (SELECT ENUMITEMNAME FROM deleted) AND
VALUEOFITEM = (SELECT VALUEOFITEM FROM deleted) AND
DESCRIPTION = (SELECT DESCRIPTION FROM deleted) AND
ENTITYID = (SELECT ENTITYID FROM deleted) AND
CHANGEDBY = (SELECT CHANGEDBY FROM deleted) AND
CHANGEDDATE = (SELECT CHANGEDDATE FROM deleted)
END

the primary key for the table ENUMERATED is (ENUMID ,MAJORBUILDNO, MINORBUILDNO )

(i didn't design the table just got to work out a way to use it :( making it more difficult

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-15 : 20:03:23

Nigel provided the correct syntax for multi-row trigger updates:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=43662

You cannot use embedded SELECTs like you have now.

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-15 : 20:15:23

Hey, yeh i know and it was a great solution as well

but my problem is really the if statment

at the moment using Nigel's answer i have this happening

just wondering is there a better way to do things

update ENUMERATED set
ENUMID = i.ENUMID,
MAJORBUILDNO = 0,
MINORBUILDNO = 0 ,
ENUMITEMNAME = i.ENUMITEMNAME ,
/*and so on*/
from ENUMERATED e
JOIN inserted i on
e.ENUMID= i.ENUMID AND e.MAJORBUILDNO = i.MAJORBUILDNO AND e.MINORBUILDNO = i.MINORBUILDNO
where e.MAJORBUILDNO = 0 AND e.MINORBUILDNO = 0

/*this followed by another update but the where clause is differnt this would be the normal update previous one is the speical case update*/

update ENUMERATED set
ENUMID = i.ENUMID,
MAJORBUILDNO = 0,
MINORBUILDNO = 0 ,
ENUMITEMNAME = i.ENUMITEMNAME ,
/*and so on*/
from ENUMERATED e
JOIN inserted i on
e.ENUMID= i.ENUMID AND e.MAJORBUILDNO = i.MAJORBUILDNO AND
e.MINORBUILDNO = i.MINORBUILDNO
JOIN deleted d on
e.ENUMID= d.ENUMID AND e.MAJORBUILDNO = d.MAJORBUILDNO AND
e.MINORBUILDNO = d.MINORBUILDNO
where e.MAJORBUILDNO = d.MAJORBUILDNO AND e.MINORBUILDNO d.MINORBUILDNO AND e.ENUMID = d.ENUMID

just wondering if this is efficent or is there a better way putting it into a if statment? which is where i get buggared as i am not sure how to do the test

thanks for bearing with me i kinda got thrown in the deep end with sql server still working out how many things are done, appricate the help

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-15 : 20:23:25

Change this:

IF ((SELECT MajorBuildNo FROM inserted) = 0 AND (SELECT MinorBuildNo FROM inserted) = 0)

To this:

IF EXISTS(SELECT * FROM inserted WHERE MajorBuildNo = 0 AND MinorBuildNo = 0)

That will look for at least one row that matches the criteria, and if it finds one it will return true and execute the proper code.

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-15 : 20:24:43

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-15 : 20:32:28

wait sorry just one more quesiton wont that then do the "speical update" on all the rows if at least one of the row had MAJORBUILDNO =0 and MINORBUILDNO = 0 which is not what i intended

i only want the rows with MAJORBUILDNO and MINORBUILDNO = 0 to be updated with 'Speical values' rest should be updated normally

like my example with the updates that use joins that works but just wondering if i can do it in some kind of if statment so i dont have to try execute 2 update statments.

oh and sorry they should be look like the 2nd upate with joining of the deleted table like so

update ENUMERATED set
ENUMID = i.ENUMID,
MAJORBUILDNO = i.MAJORBUILDNO ,
MINORBUILDNO = i.MINORBUILDNO ,
ENUMITEMNAME = i.ENUMITEMNAME ,
CHANGEDBY = i.CHANGEDBY ,
CHANEDATE = i.CHANEDATE
from ENUMERATED e
JOIN inserted i on
e.ENUMID= i.ENUMID AND e.MAJORBUILDNO = i.MAJORBUILDNO AND
e.MINORBUILDNO = i.MINORBUILDNO
JOIN deleted d on
e.ENUMID= d.ENUMID AND e.MAJORBUILDNO = d.MAJORBUILDNO AND
e.MINORBUILDNO = d.MINORBUILDNO
where e.MAJORBUILDNO = d.MAJORBUILDNO AND e.MINORBUILDNO = d.MINORBUILDNO AND e.ENUMID = d.ENUMID

update ENUMERATED set
ENUMID = i.ENUMID,
MAJORBUILDNO = i.MAJORBUILDNO,
MINORBUILDNO = i.MINORBUILDNO ,
ENUMITEMNAME = i.ENUMITEMNAME ,
CHANGEDBY = USER,
CHANEDATE = GETDATE()
from ENUMERATED e
JOIN inserted i on
e.ENUMID= i.ENUMID AND e.MAJORBUILDNO = i.MAJORBUILDNO AND
e.MINORBUILDNO = i.MINORBUILDNO
JOIN deleted d on
e.ENUMID= d.ENUMID AND e.MAJORBUILDNO = d.MAJORBUILDNO AND
e.MINORBUILDNO = d.MINORBUILDNO
where e.MAJORBUILDNO = 0 AND e.MINORBUILDNO = 0 AND e.ENUMID = d.ENUMID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources