CREATE TRIGGER [dbo].[tgUser_Error]
ON [dbo].[DrugH]
FOR UPDATE
AS
IF
(
    SELECT @@RowCount
) > 1
    RETURN;

DECLARE @newValue VARCHAR(20);
DECLARE @preValue VARCHAR(20);
DECLARE @userValue VARCHAR(3) = CAST(Manager.dbo.SessionUser(@@SPID, 1) AS VARCHAR(3));
DECLARE @id BIGINT;

SELECT @id = Id_Havaleh,
       @newValue = User_Error
FROM Inserted;

SELECT @preValue = User_Error
FROM Deleted;

SET @userValue = REPLICATE('0', 3 - LEN(@userValue)) + @userValue;




IF (@newValue = '12')
BEGIN
    UPDATE dbo.DrugH
    SET User_Error = CASE
                         WHEN @preValue IS NULL
                              OR @preValue = '' THEN
                             STUFF(REPLICATE('0', 17), 12, 1, '1') + @userValue
                         ELSE
                             STUFF(STUFF(@preValue, 12, 1, '1'), 18, 3, @userValue)
                     END
    WHERE Id_Havaleh = @id;
    RETURN;
END;

IF (@newValue = '11')
BEGIN

    UPDATE dbo.DrugH
    SET User_Error = CASE
                         WHEN @preValue IS NULL
                              OR @preValue = '' THEN
                             STUFF(REPLICATE('0', 17), 11, 1, '1') + @userValue
                         ELSE
                             STUFF(STUFF(@preValue, 11, 1, '1'), 18, 3, @userValue)
                     END
    WHERE Id_Havaleh = @id;
    RETURN;
END;

SET @newValue = REPLICATE('0', 20);
SET @preValue = ISNULL(@preValue, REPLICATE('0', 20));

SELECT @newValue = CASE
                       WHEN Ins.Sazman_Code <> del.Sazman_Code THEN
                           '1'
                       ELSE
                           SUBSTRING(@preValue, 1, 1)
                   END,
       @newValue = @newValue + CASE
                                   WHEN Ins.PT_Code <> del.PT_Code THEN
                                       '1'
                                   ELSE
                                       SUBSTRING(@preValue, 2, 1)
                               END,
       @newValue = @newValue + CASE
                                   WHEN Ins.Note_Code <> del.Note_Code THEN
                                       '1'
                                   ELSE
                                       SUBSTRING(@preValue, 3, 1)
                               END,
       @newValue = @newValue + CASE
                                   WHEN Ins.Resive_Name <> del.Resive_Name THEN
                                       '1'
                                   ELSE
                                       SUBSTRING(@preValue, 4, 1)
                               END,
       @newValue = @newValue + CASE
                                   WHEN Ins.Page_No <> del.Page_No THEN
                                       '1'
                                   ELSE
                                       SUBSTRING(@preValue, 5, 1)
                               END,
       @newValue = @newValue + CASE
                                   WHEN Ins.Tarikh_Noskheh <> del.Tarikh_Noskheh THEN
                                       '1'
                                   ELSE
                                       SUBSTRING(@preValue, 6, 1)
                               END,
       @newValue = @newValue + CASE
                                   WHEN Ins.Tarikh_Etebar <> del.Tarikh_Etebar THEN
                                       '1'
                                   ELSE
                                       SUBSTRING(@preValue, 7, 1)
                               END,
       @newValue = @newValue + CASE
                                   WHEN Ins.Tarikh_Paziresh <> del.Tarikh_Paziresh THEN
                                       '1'
                                   ELSE
                                       SUBSTRING(@preValue, 8, 1)
                               END,
       @newValue = @newValue + CASE
                                   WHEN Ins.Nezam_No <> del.Nezam_No THEN
                                       '1'
                                   ELSE
                                       SUBSTRING(@preValue, 9, 1)
                               END,
       @newValue = @newValue + CASE
                                   WHEN Ins.Del_Flag <> del.Del_Flag THEN
                                       '1'
                                   ELSE
                                       SUBSTRING(@preValue, 10, 1)
                               END,
       @newValue = @newValue + SUBSTRING(@preValue, 11, 2)
FROM Inserted Ins,
     Deleted del;

SET @newValue = @newValue + REPLICATE('0', 12);

IF @newValue <> REPLICATE('0', 20)
BEGIN

    SET @newValue = STUFF(@newValue, 18, 3, @userValue);
    UPDATE DrugH
    SET User_Error = @newValue
    WHERE Id_Havaleh = @id;
END;