Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
DBAshnik Member Откуда: Сообщений: 410 |
Привет всем! Имеется некая 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] Ответить | Цитировать Сообщить модератору |
aleks222 Member Откуда: Сообщений: 1244 |
set nocount on; в триггер воткните. И пребудет с вами щастье. |
26 июн 19, 17:31 [21915729] Ответить | Цитировать Сообщить модератору |
Shakill Member Откуда: мск Сообщений: 1882 |
set nocount on в триггере в начало |
26 июн 19, 17:32 [21915732] Ответить | Цитировать Сообщить модератору |
aleks222 Member Откуда: Сообщений: 1244 |
И чо оракелисты так коряво и многосложно триггера то пишут?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] Ответить | Цитировать Сообщить модератору |
a_voronin Member Откуда: Москва Сообщений: 4805 |
DBAshnik, Вы не торопитесь писать код аналогичный ORACLE. То, что хорошо для Oracle, может быть нехорошо для MSSQL. |
26 июн 19, 18:36 [21915761] Ответить | Цитировать Сообщить модератору |
Relic Hunter Member Откуда: AB Сообщений: 7482 |
DBAshnik, зачем в DWH триггеры? |
26 июн 19, 20:38 [21915822] Ответить | Цитировать Сообщить модератору |
iap Member Откуда: Москва Сообщений: 47052 |
Помню, тема была: Можно ли в триггере(for U,I,D) определить какое действие над табл. вызвало его?![]() |
26 июн 19, 21:08 [21915842] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9646 |
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] Ответить | Цитировать Сообщить модератору |
tunknown Member Откуда: Сообщений: 768 |
Возможно, нужен костыль в каждом триггере, который будет эмулировать предположительно ожидаемое приложением значение @@rowcount. |
||
27 июн 19, 09:22 [21915931] Ответить | Цитировать Сообщить модератору |
Сон Веры Павловны Member Откуда: Сообщений: 6127 |
Это дельфийские датасеты так зачастую делают при отправке изменений в базу - смотрят на сообщение rows affected, и если оно не равно 1, то выбрасывают ошибку. Обходится достаточно просто: set nocount off; declare @t table(n int); insert into @t values(null); в самом конце тела триггера/ХП. |
||
27 июн 19, 11:55 [21916056] Ответить | Цитировать Сообщить модератору |
DBAshnik Member Откуда: Сообщений: 410 |
огромное спасибо! (и Shakill-у тоже!). Ровно то что нам надо, оказывается!!! |
||
27 июн 19, 12:46 [21916118] Ответить | Цитировать Сообщить модератору |
DBAshnik Member Откуда: Сообщений: 410 |
Спасибо! Очень элегантно! (не знал, что except это аналог "нашего" MINUS) :-) |
||
27 июн 19, 12:47 [21916120] Ответить | Цитировать Сообщить модератору |
DBAshnik Member Откуда: Сообщений: 410 |
интересное решение. Спасибо! Не знал про таки вещи как: [function] и cross apply Ещё раз спасибо всем за участие! |
||
27 июн 19, 12:48 [21916124] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |