Update field with a specific condition with trigger

Question

Update field with a specific condition with trigger

Navigation

#1 by (1 votes)

1

I have the following situation I would like an idea of the condition that could be put in a trigger so that when you insert an ISR422 for example 1.1 the date is updated automatically of the one that has an isr422 = 1, that is, both would have the same date in the date field, I can continue inserting records that have an ISR422 = 1.2, 1.3 etcetera but I want it to be updated only when its decimal point is .1. So far I can not think of any ideas you may have.

id_product  product     ISR422           date
1           dr43        1               20/07/17                
2           fh28        4       
3           sd43        2               
4           fh28        3   
5           fh29        1.1             20/07/17
6           dr43        4.1

sql sql-server tsql stored-procedure trigger

asked by Ersuka6 20.07.2017 в 22:55

source

1 answer

Update app play store keeping names of the packages Controller in MVC for the same Model but different role

score 1 · Answer 1

Fundamentally what you ask is when you update the date of a record whose ISR422 column has a value of type [number] .1 is also updated the record whose value for that column is [number].

We're going to use the PARSENAME function, Available from SQL 2012, I hope it is compatible with your version. But we must look for another alternative. Basically this routine, allows to obtain the parts of an object with the point as a separator, let's see some examples:

SELECT 'PARSENAME(4.1, 1)', PARSENAME(4.1, 1) UNION
SELECT 'PARSENAME(4.1, 2)', PARSENAME(4.1, 2) UNION
SELECT 'PARSENAME(3.4, 1)', PARSENAME(3.4, 1) UNION
SELECT 'PARSENAME(3.4, 2)', PARSENAME(3.4, 2) UNION
SELECT 'PARSENAME(1, 1)', PARSENAME(1, 1) UNION
SELECT 'PARSENAME(1, 2)', PARSENAME(1, 2)

The result:

================ =====
PARSENAME(1, 1)      1
PARSENAME(1, 2)   NULL
PARSENAME(3.4, 1)    4
PARSENAME(3.4, 2)    3
PARSENAME(4.1, 1)    1
PARSENAME(4.1, 2)    4

As you can see in the examples, we are going to use this function to determine the "parent" record and also if the updated value has the indicated decimal value.

First, as an example, we created a test table:

CREATE TABLE Ejemplo (
    id_product  INT,
    product     VARCHAR(15),
    ISR422      FLOAT,
    date        DATETIME
)
GO

And we define the trigger of UPDATE (one of INSERT is very similar)

CREATE TRIGGER U_Ejemplo ON dbo.Ejemplo AFTER UPDATE
AS

    IF ((SELECT TRIGGER_NESTLEVEL()) < 2)
    BEGIN
        IF UPDATE(date)
        BEGIN
            -- Si se actualizo un 1.1 actualizamos también 1
            UPDATE Ejemplo
                SET date = I.date
                FROM Ejemplo T
                INNER JOIN INSERTED I
                    ON  T.ISR422 = PARSENAME(I.ISR422,2) -- Actualizamos el id sin parte decimal
                        AND PARSENAME(I.ISR422,1) = 1 -- Solo si la parte decimal del que hemos actualizado es .1
        END
    END

GO

We add some examples to the table

INSERT INTO Ejemplo(id_product, product, ISR422)
   VALUES (1, 'dr43', 1),
          (2, 'fh28', 4),
          (3, 'sd43', 2),
          (4, 'fh28', 3),
          (5, 'fh29', 1.1),
          (6, 'fh29', 1.2),
          (7, 'dr43', 4.1)

If we check the operation for example by updating the ISR422 = 1.1 for id_product .

UPDATE Ejemplo
    SET date = '20170720'
    WHERE id_product = 5

SELECT *
       FROM Ejemplo

And the final result

==== ======= ======= ===========================
1,00    dr43    1,00    20/07/2017 12:00:00 a.m. <- Actualizado
2,00    fh28    4,00    NULL
3,00    sd43    2,00    NULL
4,00    fh28    3,00    NULL
5,00    fh29    1,10    20/07/2017 12:00:00 a.m. <- Actualizado
6,00    fh29    1,11    NULL
7,00    dr43    4,10    NULL

Also, in case of updating another value, for example 1.11, it should only be updated and not the "father"

UPDATE Ejemplo
    SET date = '20170722'
    WHERE id_product = 6

We see that this is indeed the case

==== ======= ======= ===========================
1,00    dr43    1,00    20/07/2017 12:00:00 a.m.
2,00    fh28    4,00    NULL
3,00    sd43    2,00    NULL
4,00    fh28    3,00    NULL
5,00    fh29    1,10    20/07/2017 12:00:00 a.m.
6,00    fh29    1,11    22/07/2017 12:00:00 a.m. <- Solo se actualizo este registro
7,00    dr43    4,10    NULL

Additional notes

You did not specify the data type of this column, FLOAT, DECIMAL or possibly the precision, we are not going to consider cases like 1.1000001 of last can be rounded to a decimal so that the proposed algorithm continues to work.
The TRIGGER_NESTLEVEL() we use since we are updating on the same table, we must contemplate this to avoid a chaining of triggers.