October 1, 2018 at 11:39 am
I am quite new at these SQL Triggers so I am not sure how to approach this concept.
Here is the logic:
On an opportunity there is a stage, when the stage is inserted or changed, the same value must be inserted into another column in a table.
Table 1 has two columns, which are: OppStage_id and OppStage desc
The Opportunity table has multiple columns however i just want the value in the stage column to be put into the oppstage_desc column in Table 1
I am not sure how to do this as some forums say why do i want to store the data twice instead of just using a join:
Any guidance will be greatly appreciated.
October 3, 2018 at 4:39 am
It sounds as though you've got this back to front.
Normally Table1 would be a look up of Opportunity Stages, with OppStage_Id & OppStage_Desc.
When you create or update an Opportunity, you would save the OppStage_Id for the selected Stage from Table1, into the Opportunity table.
Then when displaying the Opportunity, you would join to Table1 on OppStage_Id, & display the matching OppStage_Desc.
On the other hand, if you are really going to create a list in Table1 of all Stages input while inserting/updating Opportunities, you could use a trigger. Is that what you really want to do?
October 4, 2018 at 2:40 am
Here is my trigger: With actual table names (above was only an example of logic)
alter TRIGGER [dbo].[TRIGGER_STAGE_INSERT]
ON [dbo].[AMGR_Opportunity_Tbl]
after insert, update
AS
BEGIN
DECLARE @STAGE VARCHAR (50)
set @STAGE = (SELECT b.Stage from INSERTED i inner join Opp_View B on i.Opp_Id = b.Opp_Id where i.Opp_Id = b.Opp_Id)
if @STAGE IN ('not started','Initial comm','Needs assessment','Proposal submission','closing phase','Commitment to buy','sale won')
BEGIN
INSERT INTO O_Stage
(Client_id,Contact_Number,O_Stage)
SELECT i.Opp_id, 0, b.Stage FROM INSERTED i
inner join opp_view B on i.Opp_Id = b.Opp_Id
where i.Opp_Type = '0' and i.Opp_Id = b.Opp_Id
End
End
GO
The results are as follows:
When i insert an opportunity, both fields have the same value. Which is good
However..
I find that if i try insert another opportunity, for the same entry - it doesnt change the valye in table 2 for example:
if i do a select * from O_stage - it only shows the first inserted record:
Client_Id | O_Stage
181004250828540240011O | Initial Comm
I need it to check if the record exists and if so Update the Stage, however if it doesnt exist (new Entry) it must insert.. The logic seems simple by doing a If Exists then update otherwise insert however i do not know how to write the syntax..
The stages are already defined in both the opportunity and the O_stage table.. Is this the wrong way to accomplish the goal? Should i leave the O_stage table blank so that when the trigger executes, it inserts the values?
October 4, 2018 at 4:01 am
i Basically need to check to see if a value already exists and if it does, then delete old value and insert new value
October 4, 2018 at 8:00 am
I don't understand why you need to do this.
If the value in O_Stage is the same as in Opportunity, why do you need to store it in O_Stage?
You can just get it from Opportunity, can't you?
October 4, 2018 at 8:05 am
I can however i need to do this in order to log actions when the stage in O_Stage changes.. As the actions are not noted when the stage in the opportunity changes
October 4, 2018 at 8:24 am
Why don't you log it when it changes in Opportunity?
October 4, 2018 at 8:30 am
the system is not designed to do that so i have to go this route.. I didnt not write the source code
October 4, 2018 at 9:45 am
OK. I'm not sure it's a good idea, but try this:
alter TRIGGER [dbo].[TRIGGER_STAGE_INSERT]
ON [dbo].[AMGR_Opportunity_Tbl]
after insert, update
AS
BEGIN
DECLARE @STAGE VARCHAR (50)
SET @STAGE = (SELECT Stage from INSERTED)
IF @STAGE IN ('not started','Initial comm','Needs assessment','Proposal submission','closing phase','Commitment to buy','sale won')
BEGIN
IF NOT EXISTS(SELECT 1 FROM O_Stage S INNER JOIN INSERTED i ON S.Client_id = i.Opp_id)
-- Insert O_Stage
INSERT INTO O_Stage
(Client_id,Contact_Number,O_Stage)
SELECT i.Opp_id, 0, @STAGE
FROM INSERTED i
WHERE i.Opp_Type = '0'
ELSE
-- Update O_Stage
UPDATE O_Stage
SET O_Stage = @STAGE
FROM INSERTED i
WHERE i.Opp_Type = '0'
AND Client_id = i.opp_id
END
END
GO
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy