September 26, 2008 at 3:29 pm
I have been asked to convert this trigger to from CURSOR based to SET Based but I am not sure how to do this:
This is the trigger as it set now:
CREATE TRIGGER dbo.trg_AddressUpdate
ON address
FOR UPDATE, INSERT
AS
SET NOCOUNT ON
DECLARE @address_id INT
DECLARE @InsertedHome VarChar(10)
DECLARE @InsertedCell VarChar(10)
DECLARE @DeletedHome VarChar(10)
DECLARE @DeletedCell VarChar(10)
/* handle multiple row updates */
DECLARE curInserted CURSOR LOCAL FAST_FORWARD FOR
SELECT i.address_id,
i.homephone as InsertedHome,
i.cellphone as InsertedCell,
d.homephone as DeletedHome,
d.cellphone as DeletedCell
FROM inserted i
LEFT OUTER JOIN deleted d ON d.address_id=i.address_id
OPEN curInserted
FETCH NEXT FROM curInserted INTO @address_id,@InsertedHome,@InsertedCell,@DeletedHome,@DeletedCell
WHILE @@FETCH_STATUS = 0
BEGIN
IF ISNULL(@InsertedHome,'') <> ISNULL(@DeletedHome,'')
BEGIN
if @InsertedHome <> '0123456789'
EXEC dbo.dmsUpdateTriggerPhoneNumberChanged @address_id, @InsertedHome, 1
END
IF ISNULL(@InsertedCell,'') <> ISNULL(@DeletedCell,'')
BEGIN
if @InsertedCell <> '0123456789'
EXEC dbo.dmsUpdateTriggerPhoneNumberChanged @address_id, @InsertedCell, 2
END
FETCH NEXT FROM curInserted INTO @address_id,@InsertedHome,@InsertedCell,@DeletedHome,@DeletedCell
END
CLOSE curInserted
DEALLOCATE curInserted
Is it even possible?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 26, 2008 at 3:44 pm
what does this do?
dmsUpdateTriggerPhoneNumberChanged
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply