Creating a trigger to run inside a SP

Question

Creating a trigger to run inside a SP

Daniel Ruiz

SSC Enthusiast

Points: 174
More actions
February 8, 2008 at 1:33 pm

#62337

I want to know how I can run a trigger that will activate another store and at the same time send parameter to the same store. I dont want to create a big store when I have both store in the same DB
---------------I want to in the first table ...............
ALTER PROC [dbo].[ops_RemoveOwners]
@OwnerID int = null,
@user-id int = null
AS
if (@OwnerID <> '')
BEGIN
DECLARE @OwnerID2 AS int
SELECT FN,LN, [ADDRESS1] INTO #temp FROM Owners
WHERE OwnerID=@OwnerID
SET @OwnerID2= (SELECT o.OwnerID FROM Owners o, #temp t WHERE o.address1 = t.address1 and o.OwnerID not in (@OwnerID ))
UPDATE OwnersPossessions
SET OwnerID = @OwnerID2
WHERE (OwnerID is not null and OwnerID <> '')
and OwnerID = @OwnerID
INSERT INTO [OwnersDB].[dbo].[OwnersDeleted]
(OwnerID,LN,FN,MN,Suffix,Address1,Address2,city ,State ,zip,Province,Country,PhoneHome,PhoneWork,Email,
SSN,SharedAddressOwnerID,DoNotCombine,LastAddressUpdate, LastPhoneUpdate, LastEmailUpdate, DateCreated,
Active, Login, Password , MergedWithID,RISEDateChanged, RISEChangedBy,RISEDateChecked,RISECheckedBy,
RISEDateIntl,RISEIntlBy, RISEDateQuestion, RISEQuestionBy, RISEDateMergeChk ,RISEMergeChkBy ,RCINumber,
Origin, DoNotEmail,cell,[UserID],[dateDeleted])
SELECT OwnerID,LN,FN,MN,Suffix,Address1,Address2,city ,State ,zip,Province,Country,PhoneHome,PhoneWork,Email,
SSN,SharedAddressOwnerID,DoNotCombine,LastAddressUpdate, LastPhoneUpdate, LastEmailUpdate, DateCreated,
Active, Login, Password , MergedWithID,RISEDateChanged, RISEChangedBy,RISEDateChecked,RISECheckedBy,
RISEDateIntl,RISEIntlBy, RISEDateQuestion, RISEQuestionBy, RISEDateMergeChk ,RISEMergeChkBy ,RCINumber,
Origin, DoNotEmail,cell ,@UserID as UserID, getdate() as dateDeleted
FROM Owners
WHERE OwnerID = @OwnerID
DELETE OWNERS
WHERE OWNERID = @OwnerID
drop table #temp
end
-----------Then from the previous store sent parameter to execute this other store modify other table ------------------------
ALTER PROC [dbo].[AR_RemoveAffinityMembers] --851237
@AffinityRewardsID int = null,
@user-id int = null
AS
DECLARE @AffinityRewardsID2 AS int
SELECT [First],[Last], [ADDRESS1] INTO #temp FROM affinityRewardsMembers
WHERE AffinityRewardsID=@AffinityRewardsID
SET @AffinityRewardsID2= (SELECT a.AffinityRewardsID FROM affinityRewardsMembers a, #temp t WHERE a.address1 = t.address1 and a.AffinityRewardsID not in (@AffinityRewardsID ))
UPDATE affinityRewardsTransactions
SET AffinityRewardsID = @AffinityRewardsID2
WHERE (AffinityRewardsID is not null and AffinityRewardsID <> '')
and AffinityRewardsID = @AffinityRewardsID
INSERT INTO [OwnersDB].[dbo].[AffinityRewardsMembersDeleted]
([AffinityRewardsID],[OwnerID],[EnrollDate],[Status],[ARMemberNum],[First],[Last],[MI],[Suffix],[Address1]
,[Address2],[City],[State],[Zip],[Phonehome],[PhoneWork],[Email],[LastAddressUpdate],[LastPhoneUpdate]
,[LastEmailUpdate],[DateCreated],[Province],[Country],[Origin],[Login],[Pwd],[CancelDate],[Canceled],[Pender]
,[LinkWithOwnersDt],[TPContNum],[TPNameKey],[DoNotEmail],[ReportPrinted],[Resort],[PC],[UserID],[dateDeleted])
SELECT [AffinityRewardsID],[OwnerID],[EnrollDate],[Status],[ARMemberNum],[First],[Last],[MI],[Suffix],[Address1]
,[Address2],[City],[State],[Zip],[Phonehome],[PhoneWork],[Email],[LastAddressUpdate],[LastPhoneUpdate]
,[LastEmailUpdate],[DateCreated],[Province],[Country],[Origin],[Login],[Pwd],[CancelDate],[Canceled],[Pender]
,[LinkWithOwnersDt],[TPContNum],[TPNameKey],[DoNotEmail],[ReportPrinted],[Resort],[PC],@UserID as UserID, getdate() as dateDeleted
FROM AffinityRewardsMembers
WHERE AffinityRewardsID =@AffinityRewardsID
DELETE AffinityRewardsMembers
WHERE AffinityRewardsID =@AffinityRewardsID
and ArMemberNum in (SELECT arMemberNum FROM AffinityRewardsMembers WHERE AffinityRewardsID = @AffinityRewardsID )
drop table #temp

Viewing 5 posts - 1 through 5 (of 5 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

I'm not real clear on what you are asking for here. It sounds to me like you want to run a nested stored procedure call to the second stored procedure? Is this correct? If so, all you need to do is run EXEC dbo.AR_RemoveAffinityMembers .

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1004693 More actions · Answer 2

You said that you wanted to pass parameters to the stored procedure in the trigger. That smacks of RBAR and single row processing which is a form of "Death by SQL" in triggers... what happens if you insert more than 1 row? Triggers fire once for each Insert/Update/Delete no matter how many rows are affected.

Bottom line is to be very careful to make your triggers capable of handling more than 1 row no matter what the process in the trigger is.

--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)

Daniel Ruiz SSC Enthusiast Points: 174 More actions · Answer 3

I was thinking during the weekend and I was getting to complicated with this, what need in fact is the just to exec the next strore must likely with some if/else validation and that will help me

Daniel Ruiz SSC Enthusiast Points: 174 More actions · Answer 4

That's what I'll do thank you both of you for the info.