Блог

Эй,

Я пытаюсь обновить некоторые столбцы в своей таблице, SQL Server 2014 и я написал некоторый код для trigger и, следовательно, вставить новые значения в новую таблицу, используя следующую процедуру кода:

 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Atrin Noori
-- =============================================
ALTER TRIGGER [dbo].[UsersOnUPDATE] 
   ON  [dbo].[Users]
   After UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    --
    DECLARE @user_key int
    SELECT @user_key = i.User_key FROM inserted i;
    --  
    IF UPDATE (User_fullname) 
    BEGIN
        DECLARE @newfullname nvarchar(MAX);
        DECLARE @oldfullname nvarchar(MAX);
        --
        SELECT @oldfullname = i.User_fullname FROM deleted i;
        SELECT @newfullname = i.User_fullname FROM inserted i;
        --
        INSERT INTO UsersLogs (UL_user_key_r, UL_date, UL_oldname, UL_newname, UL_IsDeleted)
        VALUES (@user_key, GETDATE(), @oldfullname, @newfullname, 0)
    END
    --
    ELSE IF UPDATE (User_password) 
    BEGIN
        DECLARE @newpassword nvarchar(10);
        DECLARE @oldpassword nvarchar(10);      
        -- 
        SELECT @oldpassword = i.User_password FROM deleted i;
        SELECT @newpassword = i.User_password FROM inserted i;
        --
        INSERT INTO UsersLogs (UL_user_key_r,UL_date,UL_oldpass,UL_newpass,UL_IsDeleted)
        VALUES (@user_key, GETDATE(), @oldpassword, @newpassword, 0)
    END 
    --
    ELSE IF UPDATE (User_username) 
    BEGIN
        DECLARE @newusername nvarchar(10);
        DECLARE @oldusername nvarchar(10);
        --
        SELECT @oldusername = i.User_username FROM deleted i;
        SELECT @newusername = i.User_username FROM inserted i;
        --
        INSERT INTO UsersLogs (UL_user_key_r, UL_date, UL_oldusername, UL_newusername, UL_IsDeleted)
        VALUES (@user_key, GETDATE(), @oldusername, @newusername, 0)
    END
    --
    ELSE IF UPDATE (User_position_id_r) 
    BEGIN
        DECLARE @newposid tinyint;
        DECLARE @oldposid tinyint;
        --
        SELECT @oldposid = i.User_position_id_r FROM deleted i;
        SELECT @newposid = i.User_position_id_r FROM inserted i;
        --
        INSERT INTO UsersLogs (UL_user_key_r, UL_date, UL_oldPosid, UL_newPosid, UL_IsDeleted)
        VALUES (@user_key, GETDATE(), @oldposid, @newposid, 0)
    END
END

 

Этот триггер отлично работает, когда я вручную изменяю значения в каждом столбце и вставляю их в новую вызываемую таблицу UsersLogs .
Однако это не работает нормально, когда я использую приложение c#, которое я разрабатываю, для обновления значений…

рассматривать:

Я пытаюсь изменить пароль пользователя через свое приложение и использую Stored Procedure .

Он Stored Procedure работает нормально и обновляет значения, НО триггер UserOnUpdate вставляет значение old и new User_Fullnme (не oldpassword и newpassword) в новую таблицу (UsersLgos) и устанавливает для других значение null (что нормально, если оно равно нулю).

Я имею в виду, что будет выполняться только эта часть кода:

 IF UPDATE (User_fullname) 
    BEGIN
        DECLARE @newfullname nvarchar(MAX);
        DECLARE @oldfullname nvarchar(MAX);
        --
        SELECT @oldfullname = i.User_fullname FROM deleted i;
        SELECT @newfullname = i.User_fullname FROM inserted i;
        --
        INSERT INTO UsersLogs (UL_user_key_r, UL_date, UL_oldname, UL_newname, UL_IsDeleted)
        VALUES (@user_key, GETDATE(), @oldfullname, @newfullname, 0)
    END
 

И судя по тому, что я только что сказал… Я меняю пароль и УСТАНАВЛИВАЮ условие для проверки обновленной колонки.

Но вопрос в том, почему триггер не может понять, какой столбец был обновлен через приложение ?

записка:

Я повторяю это еще раз: ОН ОТЛИЧНО РАБОТАЕТ С РУЧНЫМИ ИЗМЕНЕНИЯМИ И ОБНОВЛЕНИЯМИ

UPDATE() Функция сообщает вам только о том, присутствовал ли столбец в UPDATE инструкции, а не о том, действительно ли значение изменилось. Кроме того, te inserted и deleted таблицы могут содержать несколько строк или ни одной.

Итак, ваш триггер действительно должен выглядеть так:

 ALTER TRIGGER [dbo].[UsersOnUPDATE] 
   ON  [dbo].[Users]
   After UPDATE
AS 

SET NOCOUNT ON;

IF NOT (UPDATE(User_fullname) OR UPDATE(User_password) OR UPDATE(User_username) OR UPDATE(User_position_id_r))
    RETURN;  -- this only tells you if the column was present

INSERT INTO UsersLogs
    (UL_user_key_r, UL_date, UL_IsDeleted,
    UL_oldname, UL_newname,
    UL_oldpass, UL_newpass,
    UL_oldusername, UL_newusername,
    UL_oldPosid, UL_newPosid)
SELECT
    i.user_key, GETDATE(), 0,
    d.fullname, i.fullname,
    d.User_password, i.User_password,
    d.User_username, i.User_username,
    d.User_position_id_r, i.User_position_id_r
FROM inserted i
JOIN deleted d ON i.user_key = d.user_key
WHERE EXISTS (    -- this checks for any differences
    SELECT i.fullname, i.User_password, i.User_username, i.User_position_id_r
    EXCEPT        -- this will deal with nulls correctly
    SELECT d.fullname, d.User_password, d.User_username, d.User_position_id_r
);
 

В случае обновления только некоторых столбцов это оставляет вас с другими столбцами, имеющими одинаковые значения до и после. Но некоторые CASE выражения должны это уладить.

 ALTER TRIGGER [dbo].[UsersOnUPDATE] 
   ON  [dbo].[Users]
   After UPDATE
AS 

SET NOCOUNT ON;

IF NOT (UPDATE(User_fullname) OR UPDATE(User_password) OR UPDATE(User_username) OR UPDATE(User_position_id_r))
    RETURN;  -- this only tells you if the column was present

INSERT INTO UsersLogs
    (UL_user_key_r, UL_date, UL_IsDeleted,
    UL_oldname, UL_newname,
    UL_oldpass, UL_newpass,
    UL_oldusername, UL_newusername,
    UL_oldPosid, UL_newPosid)
SELECT
    i.user_key, GETDATE(), 0,
    CASE WHEN d.fullname <> i.fullname THEN d.fullname END, CASE WHEN d.fullname <> i.fullname THEN i.fullname END
    CASE WHEN d.User_password <> i.User_password THEN d.User_password END, CASE WHEN d.User_password <> i.User_password THEN i.User_password END,
    CASE WHEN d.User_username <> i.User_username THEN d.User_username END, CASE WHEN d.User_username <> i.User_username THEN i.User_username END,
    CASE WHEN d.User_position_id_r <> i.User_position_id_r THEN d.User_position_id_r END, CASE WHEN d.User_position_id_r <> i.User_position_id_r THEN i.User_position_id_r END
FROM inserted i
JOIN deleted d ON i.user_key = d.user_key
WHERE EXISTS (    -- this checks for any differences
    SELECT i.fullname, i.User_password, i.User_username, i.User_position_id_r
    EXCEPT        -- this will deal with nulls correctly
    SELECT d.fullname, d.User_password, d.User_username, d.User_position_id_r
);
 

Если вы хотите, чтобы для каждого измененного значения были отдельные строки, вы можете отменить вывод с помощью CROSS APPLY

update() функция триггера возвращает значение true , если столбец обновляется, даже если он имеет то же значение. таким образом, похоже, что при обновлении пароля вы обновляете столбец User_fullname и, вероятно, другие столбцы (с тем же значением, что и раньше, конечно) . поэтому UPDATE (User_fullname) возвращает значение true .

Но также и то, как вы написали свой код , триггер работает в ситуации , когда обновляется только один столбец , и в этом порядке в вашем коде , например, если UPDATE (User_fullname) верно, ваш код не проверяет другие условия из-за ELSE IF . возможно , вы захотите удалить else и проверить каждый столбец или полностью изменить свою стратегию, чтобы регистрировать данные внутри триггера.

затем , основываясь на вашем комментарии, избавьтесь от всего, если еще, и сделайте одно подобное заявление insert для всех столбцов:

 INSERT INTO UsersLogs (UL_user_key_r,UL_date,UL_oldpass,UL_newpass,<all columns>)
select (@user_key, GETDATE(), case when deleted.password <> new.password then deleted.password else null end , case when deleted.password <> new.password then inserted.password else null end , ....)
from inserted i
join deleted d on i.userkey = d.userkey