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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply