Change column value after INSERT if the value fits criteria?

Question

I have never really worked with Triggers before in MSSQL but I think it'll be what I need for this task.

The structure of the table is as such:

ID|****|****|****|****|****|****|****|TOUROPERATOR

The Tour Operator Code is the code that tells us what company owned the flight we carried out for them. Two of those codes (there are 24 in total) are outdated. Our users requested that those two be changed but the tour operator code is pulled from a database we don't control. The FlightData table however, we do control. So I was thinking a trigger could change the tour operator code if it was one of the two outdated ones, to the correct ones instead respectively when they were inserted.

So I went into good ol' SQL Management Studio and asked to make a trigger. It gave me some sample code and here is my Pseudo Code below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER ChangeProvider 
ON  FlightData
AFTER INSERT 
AS  
BEGIN 
    IF(TheInsertedValue == Criteria)
        UPDATE FlightData
        SET TheInsertedValue = NewValue
    ENDIF
END  
GO

I am not that good with this type of Database Programming so excuse my mistakes.

How would I go about doing this?

Possible duplicate of [How can I edit values of an INSERT in a trigger on SQL Server?](http://stackoverflow.com/questions/3580123/how-can-i-edit-values-of-an-insert-in-a-trigger-on-sql-server) — Liesel, Aug 12 '16 at 07:48
Why update the table after insert? Why not process the tour operator code before inserting and insert the right provider code? — The Shooter, Aug 12 '16 at 07:50
Because the code is pulled from a database we don't control. We only have view access to that database, we can't manipulate it. — OmniOwl, Aug 12 '16 at 07:52
Read about triggers and try write the code https://msdn.microsoft.com/en-us/library/ms189799.aspx Alternatively create a procedure which will process source data, correct what is need and load to persistent table — Serg, Aug 12 '16 at 07:53
@Vipar: What I understand is you get data about Flights from a database out of your control and you insert this data into FlightData table in a database under your control. This data contains tour operator code and provider code but provider code is outdated and as you say you can manipulate/extract provider code from tour operator code if it fits certain condition. Please correct me if my understanding is wrong. — The Shooter, Aug 12 '16 at 08:03
@TheShooter Almost correct. The Tour Operator Code (the provider code is not a thing, I might have said it was. That's my bad) is the code that tells us what company owned the flight we carried out for them. Two of those codes (there are 24 in total) are outdated. Our users requested that those two be changed but the tour operator code is pulled from a database we don't control. The FlightData table however, we do control. So I was thinking a trigger could change the tour operator code if it was one of the two outdated ones, to the correct ones instead respectively when they were inserted. — OmniOwl, Aug 12 '16 at 08:06
I updated the question so that it hopefully have the right description now. — OmniOwl, Aug 12 '16 at 08:16
@Vipar Since you control the FlightTable and it is the one where update has to be made I think it's better to process tour operator code and insert it rather than insert it and then update it. If one operation can do the job no need to have code to do changes at multiple places. Makes maintainance easy. Also triggers are expensive and not suggested unless there is no other way or other ways outperform triggers. — The Shooter, Aug 12 '16 at 08:30

score 1 · Accepted Answer · answered Aug 12 '16 at 08:33

1

You could add a computed column to your table instead of adding a trigger. Then the new column could just use a case statement to either show the original TourOperator column value or the new value you wanted.

You'd add a new column to your table like this

TourOperatorCorrect = CASE WHEN TourOperator = 'Whatever value' THEN     'ChangedValue'
                      --I just want to use what I have already in the TourOperator column
                      ELSE TourOperator 
                      END AS VARCHAR(50)

Basics of computed columns are here - https://msdn.microsoft.com/en-ie/library/ms188300.aspx

answered Aug 12 '16 at 08:33

Ricky Keane

1,540
2
15
21

From what I gather, I'd have to delete the column, full of data, and then make a new column beside it ._. – OmniOwl Aug 12 '16 at 08:48
You can keep the TourOperator column, you're adding another virtual column (they can be persisted) to the table which will have your "clean" value in it, either the original TourOperator value or the new value. – Ricky Keane Aug 12 '16 at 08:56
So I'd query the computed column, instead of the original one or? – OmniOwl Aug 12 '16 at 09:00
Yes, query the computed column instead of the TourOperator one. Here are some good examples of how to do it, http://blog.sqlauthority.com/2016/04/27/sql-server-computed-column-conditions-case-statement/ – Ricky Keane Aug 12 '16 at 09:04
Okay. I guess I'll take that as the best solution to this. Thanks :) – OmniOwl Aug 12 '16 at 09:14

score 0 · Answer 2 · answered Aug 12 '16 at 07:52

Your misconception here is that the trigger runs once per inserted value - it is in fact run once per insert statement, so you can and will find more than one row inserted at once.

You'll find that your inserted values are in the pseudo table inserted, which has the same structure as your FlightData table in this case. You write a select statement against that, specifying any criteria you wish.

However, it's not immediately clear what your logic is - does the FlightData table you are updating in your trigger only have one row? Do you update every row in the table with the newest inserted value? It is hard to understand what you are trying to now, and what the purpose of the table and this trigger are - let alone what you would want to do if you inserted more than one row at once.

The table FlightData, have hundreds of thousands of rows but two of the tour operator codes that we are using have been requested to be changed by our users when they pull data from that table. The tour operator codes however, are pulled from an external database we don't control. We can't manipulate it. So what I was thinking, was to catch an insert and simply change the tour operator codes so that they show correctly in the FlightData table. — OmniOwl, Aug 12 '16 at 07:57

score 0 · Answer 3 · answered Aug 12 '16 at 07:54

When inserted table contains mutiple rows,your code will fail,so change code to work with inserted table as whole

    UPDATE F
    SET f.TheInsertedValue = i.value
    from inserted i
    join
    Flighttable F
   on f.matchingcolumn=i.matchingcolumn
   and i.somevalue='criteria'

Change column value after INSERT if the value fits criteria?

3 Answers3