INSTEAD OF UPDATE Trigger and Updating the Primary Key

Question

Hi,

I am making changes to an existing database while developing new software. There is also quite a lot of legacy software that uses the database that needs to continue working, i.e. I would like to maintain the existing database tables, procs, etc.

Currently I have the table

CREATE TABLE dbo.t_station (
    tx_station_id        VARCHAR(4)    NOT NULL,
    tx_description       NVARCHAR(max) NOT NULL,
    tx_station_type      CHAR(1)       NOT NULL,
    tx_current_order_num VARCHAR(20)   NOT NULL,

    PRIMARY KEY (tx_station_id)
)

I need to include a new field in this table that refers to a Plant (production facility) and move the tx_current_order_num to another table because it is not required for all rows. So I've created new tables:-

CREATE TABLE Private.Plant (
    PlantCode   INT           NOT NULL,
    Description NVARCHAR(max) NOT NULL,

    PRIMARY KEY (PlantCode)
)
CREATE TABLE Private.Station (
    StationId   VARCHAR(4)    NOT NULL,
    Description NVARCHAR(max) NOT NULL,
    StationType CHAR(1)       NOT NULL,
    PlantCode   INT           NOT NULL,

    PRIMARY KEY (StationId),

    FOREIGN KEY (PlantCode) REFERENCES Private.Plant (PlantCode)
)
CREATE TABLE Private.StationOrder (
    StationId   VARCHAR(4)  NOT NULL,
    OrderNumber VARCHAR(20) NOT NULL,

    PRIMARY KEY (StationId)
)

Now, I don't want to have the same data in two places so I decided to change the dbo.t_station table into a view and provide instead of triggers to do the DELETE, INSERT and UPDATE. No problem I have [most of] them working.

My question regards the INSTEAD OF UPDATE trigger, updating the Primary Key column (tx_station_id) and updates to multiple rows.

Inside the trigger block, is there any way to join the inserted and deleted [psuedo] tables so that I know the 'before update primary key' and the 'after update primary key'? Something like this...

UPDATE sta
    SET sta.StationId = ins.tx_station_id
    FROM Private.Station AS sta
        INNER JOIN deleted AS del
            INNER JOIN inserted AS ins
                ON ROW_IDENTITY_OF(del) = ROW_IDENTITY_OF(ins)
            ON del.tx_station_id = sta.StationId

At this stage I've put a check in the trigger block that rollbacks the update if the primary key column is updated and there is more than one row in the inserted, or deleted, table.

Answer 1

The short answer is no.

You could put a surrogate key on Private.Station, and expose that through the view, and use that to identify before and after values. You wouldn't need to change the primary key or foreign key relationship, but you would have to expose some non-updateable cruft through the view, so that it showed up in the pseudo-tables. eg:

alter table Private.Station add StationSk int identity(1,1) not null

Note, this may break the legacy application if it uses SELECT *. INSERT statements without explicit insert column lists should be ok, though.

Short of that, there may be some undocumented & consistent ordering between INSERTED and DELETED, such that ROW_NUMBER() OVER (ORDER BY NULLIF(StationId,StationId)) would let you join the two, but I'd be very hesitant to take the route. Very, very hesitant.

Have you intentionally not enabled cascade updates? They're useful when primary key values can be updated. eg:

CREATE TABLE Private.Station (
    StationId   VARCHAR(4)    NOT NULL,
    Description NVARCHAR(max) NOT NULL,
    StationType CHAR(1)       NOT NULL,
    PlantCode   INT           NOT NULL,
    PRIMARY KEY (StationId),
    FOREIGN KEY (PlantCode) REFERENCES Private.Plant (PlantCode) 
      ON UPDATE CASCADE 
      -- maybe this too:
      -- ON DELETE CASCADE
)

Someone might have a better trick. Wait and watch!