SQL Server trigger for comparing old values with new values before insert

Question

I have an issue on creating a trigger in SQL Server because I don't know clearly how to check the new values with the old values.

I have a main table called MTRL and another one called CCCMTRL. I want to select the columns MTRL (primary key of table MTRL),COMPANY and PRICE from MTRL table before an update has been made on MTRL table and activate a trigger that must compare the new price from MTRL with the old price from CCCMTRL.

If prices are the same there is no need for an update, but if they are different there should be an insertion in the CCCMTRL

INSERT INTO CCCMTRL(MTRL,COMPANY,OLDPRICE,NEWPRICE) 
VALUES (MTRL,COMPANY,OLD.PRICE,NEW.PRICE)

something like this.

Answer 1

This can be achieved using INSTEAD OF INSERT TRIGGER.

1. Create view on top of table

2. Create INSTEAD OF INSERT Trigger on top of view.

   For more detail below url

https://technet.microsoft.com/en-us/library/ms175521%28v=sql.105%29.aspx

Answer 2

I'm trying to figure out why you want to go through the hassle of dealing with triggers. Just reformat your insert so that it takes care of you condition automatically.

I've got to assume there's some additional fields in the CCCMTRL table, one being price date change as you don't provide a condition that the price always increases or decreases, so I'm assuming that this is tracked by some sort of date field, which I've called DATEFIELD in my example query below.

Basically, this query will identify if the *new *price no longer matches the most recent old price in the CCCMTRL table (the ordering is performed by the ROW_NUMBER() windowing function). If you have any mismatches, they are inserted into the CCCMTRL table:

--Optionally insert DATEFIELD if this is not tracked via default constraint
INSERT INTO CCCMTRL(MTRL,COMPANY,OLDPRICE,NEWPRICE)
SELECT 
    t_window.MTRL, 
    t_window.COMPANY, 
    t_window.OLD_PRICE, 
    t_window.NEW_PRICE
FROM (
    SELECT 
        MTRL, 
        COMPANY, 
        OLD.PRICE AS OLD_PRICE, 
        NEW.PRICE AS NEW_PRICE, 
        ROW_NUMBER() (PARTITION BY OLD.MTRL, OLD.COMPANY ORDER BY OLD.DATEFIELD DESC) AS RowNum
    FROM CCCMTRL OLD LEFT OUTER JOIN CCCMTRL_NEW NEW
        ON  OLD.MTRL = NEW.MTRL
        AND OLD.COMPANY = NEW.COMPANY
    WHERE OLD.PRICE <> NEW.PRICE
) t_window
WHERE RowNum = 1

Below is a quick example query that effectively shows what I'm talking about with a working example. Hopefully this fixes your problem without cluttering up your DB with triggers.

SELECT COL1, COL2, DT
FROM 
(
    SELECT COL1, COL2, DT, 
    -- Create Windows Based On Colums and Assign Number based on Age of records
    ROW_NUMBER() OVER (PARTITION BY COL1, COL2 ORDER BY DT DESC) AS RowNum
    FROM
    (
        -- Row will be filtered out as it's the oldest of Liquids/Blues
        SELECT 'Liquid' AS COL1, 'Blue' AS COL2, GETDATE() - 1 AS DT
        UNION
        SELECT 'Liquid', 'Blue', GETDATE()
        UNION
        SELECT 'Liquid', 'Red', GETDATE()
        UNION
        -- Row will be filtered out as it's the oldest of Solid/Greens
        SELECT 'Solid', 'Green', GETDATE()
        UNION
        SELECT 'Solid', 'Green', GETDATE() + 2
    ) t_example
) t_windowed
WHERE RowNum = 1

Answer 3

SQL Server trigger for comparing old values with new values before insert

Not possible. SQL Server 101, clearly readable in the documentation: there is no before insert trigger.

You have only an on insert (AFTER insert) and INSTEAD OF (which means no insert, but you can do the insert in the trigger, after checking).

In your case an after insert trigger would also work.