Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
PisarevskiyRE
Member

Откуда:
Сообщений: 12
Добрый день, коллеги.

Второй день ломаю голову по поводу вот такой задачи -
Есть таблица orders, триггер натравлен на эту таблицу.
Сценарий такой - про добавлении строки в заказы пишется лог в таблицу orders_log и insert двух строк
в вспомогательную таблицу.

Вопрос к знатокам - почему иногда примерно 1000 к 1 в таблице inserted пропадают значения???

К примеру -
1. Создаем заказ (insert into orders)
2. Триггер агрится и начинает выполнение.
3. Достаем значение из inserted и вставляем в лог. Ок.
4. Достаем значение из inserted и вставляем в вспомогательную. Не ок. Строка не создается.

Для понимаю вставлю запрос.

select o.id, dbo.DateToDateStr( o.CREATIONDATETIME), vg.id, vg.* from orders o
left join VG_FIELD_VALUES vg on vg.REF = o.id and FIELDID = 290 
where  CREATIONDATETIME >= 43195.4747560069 
order by o.CREATIONDATETIME desc, o.ID desc


2611730 05.04.2018 15:10:46 84413792 84413792 NewOrder 2611730 0 1 290
2611729 05.04.2018 15:10:45 84413763 84413763 NewOrder 2611729 0 1 290
2611728 05.04.2018 15:10:42 84413735 84413735 NewOrder 2611728 0 1 290
2611726 05.04.2018 15:10:39 NULL NULL NULL NULL NULL NULL NULL
2611727 05.04.2018 15:10:39 84413684 84413684 NewOrder 2611727 0 1 290
2611725 05.04.2018 15:10:36 84413653 84413653 NewOrder 2611725 0 1 290
2611724 05.04.2018 15:10:34 84413625 84413625 NewOrder 2611724 0 1 290

Тело триггера выглядит вот так -

SET NOCOUNT ON;
   DECLARE @I BIT = 0, @D BIT = 0, @U BIT = 0, @insert_id bigint = 0, @insert_clientid bigint = 0
    IF EXISTS (SELECT TOP 1 * FROM INSERTED)
    BEGIN
        IF EXISTS (SELECT TOP 1 * FROM DELETED)
        BEGIN    
            SET @U = 1
        END
        ELSE
        BEGIN
            SET @I = 1
        END
    END
    ELSE
    BEGIN
        SET @D = 1
    END  
    IF @D = 1
    BEGIN
        INSERT INTO dbo.ORDERS_log (ChangedID, ChangedOperation)
        SELECT DISTINCT d.ID, 'D'
        FROM deleted d
    END  
    IF @I = 1
    BEGIN
        SELECT @insert_id = i.ID, @insert_clientid = i.clientid FROM inserted i
        INSERT INTO dbo.ORDERS_log (ChangedID, ChangedOperation)
        SELECT @insert_id, 'I'

		insert into VG_FIELD_VALUES (FORMNAME, REF, SVALUE, ENABLED, FIELDID)    
		VALUES
		('NewOrder',@insert_id,CAST(CAST(ROUND(dbo.GetMaxAutoSum(@insert_id), 0) AS VARCHAR(255)) AS TEXT),1,290),
		('NewOrder',@insert_id,CAST(CAST(@insert_clientid ^ 0xdeadee AS VARCHAR(255)) AS TEXT),1,286)
    END 
    IF @U = 1
    BEGIN
        INSERT INTO dbo.ORDERS_log (ChangedID, ChangedOperation)
        SELECT DISTINCT i.ID, 'U'
        FROM inserted i
    END


Определяется тип произведенной операции и производятся действия.
Вот чего я не понимаю почему insert в ORDERS_log выполняется всегда, а следующая вставка 1 к 1000 заканчивается нулами???
Нагрузка на сервер довольно таки большая.

К сообщению приложен файл. Размер - 134Kb
5 апр 18, 15:19    [21315713]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
inserted может содержать больше чем 1 запись
5 апр 18, 15:29    [21315760]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
PisarevskiyRE
Member

Откуда:
Сообщений: 12
Maxx, не в этом дело. До этого было 3 отдельные вставки без переменных, напрямую из таблицы inserted.
5 апр 18, 15:45    [21315821]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
Гигабайт Мегабайтович Килобайтов
Member [заблокирован]

Откуда:
Сообщений: 5975
ну не волшебство же происходит ))
отсюда два варианта : либо в @insert_id оказывается null, либо в @insert_id оказывается не тот id заказа который вы ожидаете ))
а это может получиться из этого
SELECT @insert_id = i.ID, @insert_clientid = i.clientid FROM inserted i
5 апр 18, 16:10    [21315922]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
PisarevskiyRE
Member

Откуда:
Сообщений: 12
Гигабайт Мегабайтович Килобайтов, я из-за этого тему и создал.

я пробовал еще 2 варианта


        INSERT INTO dbo.ORDERS_log (ChangedID, ChangedOperation)
        SELECT i.id, 'I' from inserted i

        insert into VG_FIELD_VALUES (FORMNAME, REF, SVALUE, ENABLED, FIELDID)    
	select 'NewOrder', i.id,CAST(CAST(ROUND(dbo.GetMaxAutoSum(@insert_id), 0) AS VARCHAR(255)) AS TEXT),1,290 from inserted i

        insert into VG_FIELD_VALUES (FORMNAME, REF, SVALUE, ENABLED, FIELDID)    
	select  'NewOrder',i.id ,CAST(CAST(i.client_id ^ 0xdeadee AS VARCHAR(255)) AS TEXT),1,286)


Был еще union но он вел себя так же.
5 апр 18, 16:49    [21316081]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
blonduser
Member

Откуда:
Сообщений: 132
PisarevskiyRE,

Мне одному кажется странным, что эти данные вставляются
('NewOrder',@insert_id,CAST(CAST(ROUND(dbo.GetMaxAutoSum(@insert_id), 0) AS VARCHAR(255)) AS TEXT),1,290),

а эти нет
('NewOrder',@insert_id,CAST(CAST(@insert_clientid ^ 0xdeadee AS VARCHAR(255)) AS TEXT),1,286)
5 апр 18, 16:53    [21316095]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7768
PisarevskiyRE,

а откуда уверенность, что пустые - это не update операции?
5 апр 18, 16:57    [21316106]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
PisarevskiyRE
Member

Откуда:
Сообщений: 12
blonduser, from inserted строка существует и записывается, следующие 2 оператора пропускается. Повторяюсь это случай 1/1000. И когда это проходит по времени вставки видно что транзакция ожидает чего - то.
5 апр 18, 17:17    [21316160]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
blonduser
Member

Откуда:
Сообщений: 132
PisarevskiyRE,

В триггере они у вас выглядят так
		insert into VG_FIELD_VALUES (FORMNAME, REF, SVALUE, ENABLED, FIELDID)    
		VALUES
		('NewOrder',@insert_id,CAST(CAST(ROUND(dbo.GetMaxAutoSum(@insert_id), 0) AS VARCHAR(255)) AS TEXT),1,290),
		('NewOrder',@insert_id,CAST(CAST(@insert_clientid ^ 0xdeadee AS VARCHAR(255)) AS TEXT),1,286)
5 апр 18, 17:22    [21316173]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
blonduser
Member

Откуда:
Сообщений: 132
blonduser,
Что должно пропустится?
5 апр 18, 17:22    [21316176]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
Konst_One
Member

Откуда:
Сообщений: 11520
а покажите скрипт вашей таблички VG_FIELD_VALUES
5 апр 18, 17:29    [21316200]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
PisarevskiyRE
Member

Откуда:
Сообщений: 12
Konst_One, REATE TABLE [dbo].[VG_FIELD_VALUES](
[ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[FORMNAME] [varchar](500) NULL,
[REF] [int] NULL,
[SVALUE] [text] NULL,
[ENABLED] [smallint] NULL,
[FIELDID] [int] NULL,
CONSTRAINT [PK__VG_FIELD_VALUES__3A379A64] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DataFiles]
) ON [DataFiles] TEXTIMAGE_ON [DataFiles]
GO

ALTER TABLE [dbo].[VG_FIELD_VALUES] ADD CONSTRAINT [DF__VG_FIELD___ENABL__3B2BBE9D] DEFAULT ((1)) FOR [ENABLED]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ссылка на Vg_Fields.Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'VG_FIELD_VALUES', @level2type=N'COLUMN',@level2name=N'FIELDID'
GO

К сообщению приложен файл. Размер - 44Kb
5 апр 18, 17:43    [21316249]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
Konst_One
Member

Откуда:
Сообщений: 11520
простите, а какая версия у вас сервера?

select @@version
5 апр 18, 17:48    [21316271]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
PisarevskiyRE
Member

Откуда:
Сообщений: 12
Konst_One,
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
5 апр 18, 17:49    [21316278]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
Konst_One
Member

Откуда:
Сообщений: 11520
вы делаете неверные выводы, потому что анализируете неверный запрос:

select 
   o.id, 
   dbo.DateToDateStr( o.CREATIONDATETIME), 
   vg.id, vg.* 
from orders o left join VG_FIELD_VALUES vg on vg.REF = o.id and FIELDID = 290 
where  CREATIONDATETIME >= 43195.4747560069 
order by o.CREATIONDATETIME desc, o.ID desc


в табличке VG_FIELD_VALUES нет никакой записи с выделенным вами ID
5 апр 18, 17:52    [21316288]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
Konst_One
Member

Откуда:
Сообщений: 11520
а это вы так ищите что нет записи в этой табличке. тогда отбой

зы
вы уверены что у вас удаление не происходит?
5 апр 18, 18:00    [21316340]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
PisarevskiyRE
Member

Откуда:
Сообщений: 12
Konst_One, дык я выделил просто для наглядности в скрине.
Самое простое объяснение идет подряд 3 inserta из inserted.
1, 2, ..,56,..509,..840,.. и рамдомно не пишется, причем если смотреть по id она должна была вставиться гооораздо раньше.

INSERT INTO dbo.ORDERS_log (ChangedID, ChangedOperation)
SELECT @insert_id, 'I'

insert into VG_FIELD_VALUES (FORMNAME, REF, SVALUE, ENABLED, FIELDID)
VALUES
('NewOrder',@insert_id,CAST(CAST(ROUND(dbo.GetMaxAutoSum(@insert_id), 0) AS VARCHAR(255)) AS TEXT),1,290),
('NewOrder',@insert_id,CAST(CAST(@insert_clientid ^ 0xdeadee AS VARCHAR(255)) AS TEXT),1,286)

-- можно так можно через union all можно разбить на 2 отдельных, сути не меняет записи после 1 операции не создаются

К сообщению приложен файл. Размер - 147Kb
5 апр 18, 18:05    [21316356]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
PisarevskiyRE
Member

Откуда:
Сообщений: 12
PisarevskiyRE, извиняюсь за фото. В общем случаи получается такая картина -

К сообщению приложен файл. Размер - 24Kb
5 апр 18, 18:09    [21316368]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
blonduser
Member

Откуда:
Сообщений: 132
PisarevskiyRE,

Что возвращает функция dbo.GetMaxAutoSum(@insert_id) ?
Может в ней ошибка?
5 апр 18, 19:44    [21316606]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31364
PisarevskiyRE
PisarevskiyRE, извиняюсь за фото. В общем случаи получается такая картина -
Что вы гадаете с своими запутанными значениями? Кто так отлаживает???

Навставляйте отладочных селектов в триггер, и потом вставляйте в Order, сразу всё увидите.
5 апр 18, 19:45    [21316611]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
iap
Member

Откуда: Москва
Сообщений: 46981
PisarevskiyRE,

1. Никаких переменных в триггере! Ну, разве что переменную с обозначением команда, для которой вызван триггер.
2. С inserted и deleted работать как с таблицами, а не источниками скалярных значений.
3. Не применять @@ROWCOUNT на входе триггера. Только EXISTS(SELECT * FROM inserted UNION ALL SELECT * FROM deleted).
Это на случай, если таблицу будут использовать в MERGE.
4. Забыть о типе TEXT. Вместо него - VARCHAR(MAX).
5. Неплохо бы логгировать только данные, которые изменились.
Хорошо в этом деле зарекомендовала себя конструкция FROM inserted i FULL JOIN deleted d ON i.ID=d.ID WHERE NOT EXISTS(SELECT i.Field1, ... , i.FieldN INTERSECT SELECT d.Field1, ... , d.FieldN),
где ID - первичный ключ.
6. Логгировать можно только из deleted, так как inserted и так лежит в таблице.

Всё настолько просто, что непонятно, что тут можно отлаживать.
5 апр 18, 20:37    [21316716]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2396
PisarevskiyRE,
что вернет запрос?

select * from orders_log where changedID= 2593660
6 апр 18, 10:19    [21317776]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
PisarevskiyRE
Member

Откуда:
Сообщений: 12
blonduser, функция возвращает число int.

alexeyvg, спасибо за совет, но это нельзя осуществить так как база прода, на стейджах повторить не давалось.

iap, спасибо, запомнил.

StarikNavy, приложил скрин.

***************************

Проблема решена.
Оказалось все намного тривиальнее.
Строки удаляет сервис после срабатывания триггера обычным delete.
Чудес не бывает. Отменил вызов команды Рен-ТВ :)

К сообщению приложен файл. Размер - 121Kb
6 апр 18, 17:10    [21319568]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
04cf9f9576a6f15
Member [заблокирован]

Откуда:
Сообщений: 2927
iap
1. Никаких переменных в триггере! Ну, разве что переменную с обозначением команда, для которой вызван триггер.
Почему?

#Хэш=
14 апр 18, 14:20    [21339184]     Ответить | Цитировать Сообщить модератору
 Re: Поведение триггера AFTER INSERT,DELETE,UPDATE  [new]
iap
Member

Откуда: Москва
Сообщений: 46981
04cf9f9576a6f15
iap
1. Никаких переменных в триггере! Ну, разве что переменную с обозначением команда, для которой вызван триггер.
Почему?

#Хэш=
Потому что в скалярную переменную можно поместить только одно значение
(обычно мы это и наблюдаем в триггерах новичков),
в то время как inserted и deleted - это таблицы с миллионами строк! (Специально утрирую).
Скалярная переменная провоцирует новичка писать совершенно некорректный код
для обработки таблиц inserted и deleted.
14 апр 18, 15:36    [21339302]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить