Sql Trigger Assistance for Newbie

1

HI There..

This is what i am trying to do:
I have a sales opportunity (or deal) and when i insert or change the stage of this opportunity, this table has multiple columns however i only want the stage to be inserted into another table or field. The other field is called OppsStage and the columns here are:

Column 1 - client_id
Column 2 - OppStage
Column 3 - Type_id

I am new at these triggers, i have only done one that is of a similar concept. (creates a new quote number when a new opportunity is created) but now i need assistance on how to achieve the above with the stages..

I dont know if i must use something like the below:

Declare @Stage varchar (50)

BEGIN
SET ROWCOUNT 0
SET NOCOUNT ON

IF EXISTS (SELECT 1
FROM Opp_View
WHERE Stage = @Stage)
BEGIN
SET @Stage = 'Not Started';
END
ELSE
BEGIN
SET @Stage = 'Communication';
END

if @Stage = 'not started'
INSERT INTO O_OppStage(Client_id,Contact_Number,O_OppStage)
SELECT inserted.Opp_Id, 0, @Stage
FROM inserted inner join Opp_View B on inserted.Opp_Id = b.Opp_Id
WHERE inserted.Opp_Type = 0
and @Stage = 'Not Started'
else if @Stage = 'Initial communication'
INSERT INTO O_OppStage(Client_id,Contact_Number,O_OppStage)
SELECT inserted.Opp_Id, 0, @Stage
FROM inserted inner join Opp_View B on inserted.Opp_Id = b.Opp_Id
WHERE inserted.Opp_Type = 0
and @Stage = 'Initial communication'

end

end

or if i must do this approach, with if stage = 'not started' then insert into ..

The goal is to achieve the following:

When the stage is inserted into a new row in the opportunities table, that stage is also inserted into the other table.

So like a mirror..

so if stage = stage 1 in the opportunities table then stage in table O_OppStage also needs to be stage 1

Any guidance would be greatly appreciated

2

why do they need to be the same? This is defeating the purpose of RI. Now you have to change it in 2 places. There are a couple of ways to handle this. The obvious would be to have the application that inserts/updates the first table to do the same to the second.

I don't understand your trigger. It looks like it's doing the same thing regardless of what @Stage is. is this just an insert trigger to put records into second table for history? What about updates?

3

HI Mike

This is an insert and update trigger. So when Stage changes in one table, it must replicate and insert or update the second table.

So if i click stage 1 in table one, table two must automatically update to the stage i picked in table 1.

4

Some key details missing, but this should at least get you started. I've done only the INSERT trigger so far, since I don't understand well enough to try the UPDATE part yet.

CREATE TRIGGER Oppportunies__TR_INSERT
ON dbo.Opportunities
AFTER INSERT
AS
SET NOCOUNT ON;

INSERT INTO O_OppStage(Client_id,Contact_Number,O_OppStage)
SELECT i.Opp_Id, 0, i.OppStage
FROM inserted i
inner join Opp_View B on i.Opp_Id = b.Opp_Id
WHERE i.Opp_Type = 0 AND i.OppStage IN ('Initial communication', 'Not started')
GO
1 Like
5

HI Scott - Thank you for the reply.

Here is my Trigger code:

alter TRIGGER [dbo].[TRIGGER_STAGE_INSERT]
  ON [dbo].[AMGR_Opportunity_Tbl]
  after insert, update
AS
BEGIN

  SET NOCOUNT ON;
  DECLARE @STAGE VARCHAR (50)
 

  set @STAGE = (SELECT b.Stage from INSERTED i inner join Opp_View B on 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:

  1. 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..

Would it just be:

If exists ( select Opp_id from inserted when stage = @stage)
update O_stage set O_stage = @stage

Trying to explain as much as i can

6

i Basically need to check to see if a value already exists and if it does, then delete old value and insert new value

7

Try MERGE