Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Плач ораклиста или как скомпенсировать безумие сторонней программы в триггере!  [new]
DBAshnik
Member

Откуда:
Сообщений: 358
Привет всем!

Имеется некая msSql-база, не наша, но нам надлежит её администрировать и собирать изменения с целого множества "нативных" таблиц в созданную нами в ней таблицу cap_4_dwh.
Имеется большое множество созданных нами триггеров примерно такого типа:

CREATE OR ALTER TRIGGER TR_OAE_KOMPONENT_cap ON DBO.OAE_KOMPONENT FOR DELETE, UPDATE, INSERT AS 
BEGIN 
  DECLARE @l_function VARCHAR( 99 ); 
  SET @l_function = 'UPDATE'; 
  if NOT EXISTS (SELECT * FROM deleted) 
    SET @l_function = 'INSERT'; 
  if NOT EXISTS (SELECT * FROM inserted) 
    SET @l_function = 'DELETE'; 
  if NOT EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
    SET @l_function = 'NONE'; 
  --//. 
  if @l_function = 'INSERT' 
    insert into cap_4_dwh( tablename,  "function", type, cap_status,  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate )     
    select 'OAE_KOMPONENT',  @l_function, 'AFTER', 'OPEN',  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate
      from inserted ; 
  else if @l_function = 'DELETE' 
    insert into cap_4_dwh( tablename,  "function", type, cap_status,  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate ) 
    select 'OAE_KOMPONENT',  @l_function, 'PRE', 'OPEN',  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate
      from deleted ; 
  else if @l_function = 'UPDATE' 
  BEGIN
    insert into cap_4_dwh ( tablename,  "function", type, cap_status,  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate ) 
    select 'OAE_KOMPONENT',  @l_function, 'PRE', 'OPEN', d. cmpCode, d.code, d.kmplevel, d.name, d.sName, d.accountType, d.statUser, d.delDate
      from deleted d, inserted i 
     where d.cmpcode = i.cmpcode and d.code = i.code and d.kmplevel = i.kmplevel
       and (  d.sname != i.sname or  d.accounttype != i.accounttype or  d.statuser != i.statuser or 
              d.name != i.name or  d.deldate != i.deldate or ( d.deldate is NULL and i.deldate is NOT null ) or ( d.deldate is NOT null and i.deldate is NULL )  );
    insert into cap_4_dwh ( tablename,  "function", type, cap_status,  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate ) 
    select 'OAE_KOMPONENT',  @l_function, 'AFTER', 'OPEN', i. cmpCode, i.code, i.kmplevel, i.name, i.sName, i.accountType, i.statUser, i.delDate
      from deleted d, inserted i 
     where d.cmpcode = i.cmpcode and d.code = i.code and d.kmplevel = i.kmplevel
       and (  d.sname != i.sname or  d.accounttype != i.accounttype or  d.statuser != i.statuser or 
              d.name != i.name or  d.deldate != i.deldate or ( d.deldate is NULL and i.deldate is NOT null ) or ( d.deldate is NOT null and i.deldate is NULL )  );
  END;
END; 


Поля cmpcode, code, kmplevel имеют Unique-констрейнт (что-то типа неформального PK таблицы OAE_KOMPONENT!).

Главное приложение системы абсолютно закрыто для нас, сделано в другой стране, и не то что попросить что-то поменять, спросить там даже некого! Приложение переезжает сейчас с Oracle на MsSQL (типа db-независимое.... ;-) ). Базу сейчас мигрируем. Наши старые работающие oracle-триггеры (все были row-based, если кому-то это тут что-то говорит) переписаны нами на mssql - типа
вышеуказанного примера.
Тригерры успешно оттестованы sql-DML-ями (типа "update DBO.OAE_KOMPONENT set ... where ..." ). Но когда начали тестовать глав.приложение полезли траблы!

А именно, если скажем тестовый "update DBO.OAE_KOMPONENT ..." обновляет 3 строки и апдейт содержит поля указанные в "where d.cmpcode = i.cmpcode..." то MS-Studion (Microsoft SQL Server Management Studio)
печатает примерно такие строки:

3 rows updated

6 rows inserted


Последние, понятное дело, из триггера (от вставки 3 старых и 3 новых = итого 6) записей в нашу cap_4_dwh. Пока всё хорошо.

Однако если в апдейте только поля НЕ участвующие в нашем списке ("where d.cmpcode = i.cmpcode..."). То в Studio наблюдаем соответсвенно:

3 rows updated

0 rows inserted


Так вот, эксперементальным путём мы установили, что идиотское приложение очевидно как-то ловит этот 0 и выдаёт юзеру ошибку на "морду" типа: "не удалось обновить таблицу!" .... (жесть, да_! Но нам, как сказал, на это не повлиять, увы!)

Вопрос: есть ли какое-то более приятное решение, чем отказаться совсем от "where d.cmpcode = i.cmpcode..." и протоколлировать все-все измения по-любым полям (хоть они и не нужны, дополнительно нагружают систему,
жрут дисковое пространство и т.п.!)
Без хороших знаний MS-SQL (а мы тут все ораклисты :-) ) шансов мало, ИМХО. Так что заранее благодарю за любую полезную инфу!!!
26 июн 19, 17:24    [21915723]     Ответить | Цитировать Сообщить модератору
 Re: Плач ораклиста или как скомпенсировать безумие сторонней программы в триггере!  [new]
aleks222
Member

Откуда:
Сообщений: 920
set nocount on;

в триггер воткните.
И пребудет с вами щастье.
26 июн 19, 17:31    [21915729]     Ответить | Цитировать Сообщить модератору
 Re: Плач ораклиста или как скомпенсировать безумие сторонней программы в триггере!  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
set nocount on в триггере в начало
26 июн 19, 17:32    [21915732]     Ответить | Цитировать Сообщить модератору
 Re: Плач ораклиста или как скомпенсировать безумие сторонней программы в триггере!  [new]
aleks222
Member

Откуда:
Сообщений: 920
И чо оракелисты так коряво и многосложно триггера то пишут?

set nocount on;

with l as ( select l_function = case when EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) then 'UPDATE' 
                                     when EXISTS (SELECT * FROM inserted) then 'INSERT'
                                     when EXISTS (SELECT * FROM deleted ) then 'DELETE'
									 else 'NONE'
								end
          )
insert into cap_4_dwh ( tablename,  "function", cap_status, type,  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate ) 
   select 'OAE_KOMPONENT',  (select l_function from l),  'OPEN', *
      from (
			   select 'PRE',  *
				 from (
						select  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate from deleted
						except 
						select  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate from inserted
					  ) as x
			   union all
			   select 'AFTER', *
				 from (
						select  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate from inserted
						except 
						select  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate from deleted
					  ) as x
		   ) as y
26 июн 19, 17:53    [21915742]     Ответить | Цитировать Сообщить модератору
 Re: Плач ораклиста или как скомпенсировать безумие сторонней программы в триггере!  [new]
a_voronin
Member

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

Вы не торопитесь писать код аналогичный ORACLE. То, что хорошо для Oracle, может быть нехорошо для MSSQL.
26 июн 19, 18:36    [21915761]     Ответить | Цитировать Сообщить модератору
 Re: Плач ораклиста или как скомпенсировать безумие сторонней программы в триггере!  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7316
DBAshnik,

зачем в DWH триггеры?
26 июн 19, 20:38    [21915822]     Ответить | Цитировать Сообщить модератору
 Re: Плач ораклиста или как скомпенсировать безумие сторонней программы в триггере!  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Помню, тема была: Можно ли в триггере(for U,I,D) определить какое действие над табл. вызвало его?
26 июн 19, 21:08    [21915842]     Ответить | Цитировать Сообщить модератору
 Re: Плач ораклиста или как скомпенсировать безумие сторонней программы в триггере!  [new]
invm
Member

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

То, что вам нужно делается гораздо проще и с меньшим потреблением ресурсов.
Примерно так:
use tempdb;
go

create table dbo.t (id int primary key, a int, b int, c int);
create table dbo.t_log (id int identity primary key, [function] varchar(30), [type] varchar(30), t_id int, a int, b int, c int, dt datetime2 default sysdatetime());
go

create trigger dbo.tr_t__log
on dbo.t
after insert, update, delete
as
begin
 set nocount on;

 insert into dbo.t_log
  ([function], [type], t_id, a, b, c)
 select
  t.[function], t.[type], t.id, t.a, t.b, t.c
 from
  inserted i full join
  deleted d on d.id = i.id cross apply
  (
   select 'after', 'open', i.id, i.a, i.b, i.c where i.id is not null
   union all
   select 'pre', 'open', d.id, d.a, d.b, d.c where d.id is not null
  ) t([function], [type], id, a, b, c)
 where
  not exists(
   select i.id, i.a, i.b, i.c
   intersect
   select d.id, d.a, d.b, d.c
  );
end;
go

insert into dbo.t (id, a, b, c) values (1, 1, 1, 1);
insert into dbo.t (id, a, b, c) values (2, 2, 2, 2);

delete from dbo.t where id = 1;

update dbo.t set b = 40 where id = 2;
update dbo.t set b = 40 where id = 2;
update dbo.t set id = 3 where id = 2;

select * from dbo.t_log order by id;
go

drop table dbo.t, dbo.t_log;
go
26 июн 19, 22:48    [21915862]     Ответить | Цитировать Сообщить модератору
 Re: Плач ораклиста или как скомпенсировать безумие сторонней программы в триггере!  [new]
tunknown
Member

Откуда:
Сообщений: 759
DBAshnik
3 rows updated

0 rows inserted


Так вот, эксперементальным путём мы установили, что идиотское приложение очевидно как-то ловит этот 0 и выдаёт юзеру ошибку на "морду" типа: "не удалось обновить таблицу!" .... (жесть, да_! Но нам, как сказал, на это не повлиять, увы!)
Примените mssql profiler. Посмотрите, выполняет ли что приложение после запроса на изменение. Тогда станет менее неясно, что делать.

Возможно, нужен костыль в каждом триггере, который будет эмулировать предположительно ожидаемое приложением значение @@rowcount.
27 июн 19, 09:22    [21915931]     Ответить | Цитировать Сообщить модератору
 Re: Плач ораклиста или как скомпенсировать безумие сторонней программы в триггере!  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5920
DBAshnik
Так вот, эксперементальным путём мы установили, что идиотское приложение очевидно как-то ловит этот 0 и выдаёт юзеру ошибку на "морду" типа: "не удалось обновить таблицу!" .... (жесть, да_! Но нам, как сказал, на это не повлиять, увы!)

Это дельфийские датасеты так зачастую делают при отправке изменений в базу - смотрят на сообщение rows affected, и если оно не равно 1, то выбрасывают ошибку. Обходится достаточно просто:
set nocount off;
declare @t table(n int);
insert into @t values(null);

в самом конце тела триггера/ХП.
27 июн 19, 11:55    [21916056]     Ответить | Цитировать Сообщить модератору
 Re: Плач ораклиста или как скомпенсировать безумие сторонней программы в триггере!  [new]
DBAshnik
Member

Откуда:
Сообщений: 358
aleks222
set nocount on;

в триггер воткните.
И пребудет с вами щастье.


огромное спасибо! (и Shakill-у тоже!). Ровно то что нам надо, оказывается!!!
27 июн 19, 12:46    [21916118]     Ответить | Цитировать Сообщить модератору
 Re: Плач ораклиста или как скомпенсировать безумие сторонней программы в триггере!  [new]
DBAshnik
Member

Откуда:
Сообщений: 358
aleks222
И чо оракелисты так коряво и многосложно триггера то пишут?

set nocount on;

with l as ( select l_function = case when EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) then 'UPDATE' 
                                     when EXISTS (SELECT * FROM inserted) then 'INSERT'
                                     when EXISTS (SELECT * FROM deleted ) then 'DELETE'
									 else 'NONE'
								end
          )
insert into cap_4_dwh ( tablename,  "function", cap_status, type,  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate ) 
   select 'OAE_KOMPONENT',  (select l_function from l),  'OPEN', *
      from (
			   select 'PRE',  *
				 from (
						select  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate from deleted
						except 
						select  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate from inserted
					  ) as x
			   union all
			   select 'AFTER', *
				 from (
						select  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate from inserted
						except 
						select  cmpCode, code, kmplevel, name, sName, accountType, statUser, delDate from deleted
					  ) as x
		   ) as y


Спасибо! Очень элегантно! (не знал, что except это аналог "нашего" MINUS) :-)
27 июн 19, 12:47    [21916120]     Ответить | Цитировать Сообщить модератору
 Re: Плач ораклиста или как скомпенсировать безумие сторонней программы в триггере!  [new]
DBAshnik
Member

Откуда:
Сообщений: 358
invm
DBAshnik,

То, что вам нужно делается гораздо проще и с меньшим потреблением ресурсов.
Примерно так:
use tempdb;
go

create table dbo.t (id int primary key, a int, b int, c int);
create table dbo.t_log (id int identity primary key, [function] varchar(30), [type] varchar(30), t_id int, a int, b int, c int, dt datetime2 default sysdatetime());
go

create trigger dbo.tr_t__log
on dbo.t
after insert, update, delete
as
begin
 set nocount on;

 insert into dbo.t_log
  ([function], [type], t_id, a, b, c)
 select
  t.[function], t.[type], t.id, t.a, t.b, t.c
 from
  inserted i full join
  deleted d on d.id = i.id cross apply
  (
   select 'after', 'open', i.id, i.a, i.b, i.c where i.id is not null
   union all
   select 'pre', 'open', d.id, d.a, d.b, d.c where d.id is not null
  ) t([function], [type], id, a, b, c)
 where
  not exists(
   select i.id, i.a, i.b, i.c
   intersect
   select d.id, d.a, d.b, d.c
  );
end;
go

insert into dbo.t (id, a, b, c) values (1, 1, 1, 1);
insert into dbo.t (id, a, b, c) values (2, 2, 2, 2);

delete from dbo.t where id = 1;

update dbo.t set b = 40 where id = 2;
update dbo.t set b = 40 where id = 2;
update dbo.t set id = 3 where id = 2;

select * from dbo.t_log order by id;
go

drop table dbo.t, dbo.t_log;
go


интересное решение. Спасибо! Не знал про таки вещи как: [function] и cross apply

Ещё раз спасибо всем за участие!
27 июн 19, 12:48    [21916124]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить